Variable Pacing using rangesum or sum with set

[Anonymous]

Hi all,

I am trying to get a function together to get an idea of how I am doing in terms of getting orders

Sample Data

Date	Date Month	Date Year	Delivery Month	Business Days Remaining in Month	Order Quantity
1/23/2018	1	2018	2	7	10
1/23/2018	1	2018	3	7	5
1/23/2018	1	2018	2	7	16
1/15/2018	1	2018	2	13	20
12/29/2017	12	2017	2	1	10
12/15/2017	12	2017	2	8	5
12/19/2017	12	2017	1	7	50
12/15/2017	12	2017	1	8	10

etc.

The output I would like to see is

With 7 days remaining in January --> how many orders do we have (irregardless of month) and where were we at compared to December at the same time?

Final output desired --> using delivery month of date month +1

Date Year	Date Month	Delivery Month	Business Days	Total Orders
2018	1	2	7	61
2017	12	1	7	60

So far I have tried the following equations --> which work only for current month and not for prior month or on year changes (i.e. looking at January in December)

Sum({<[Delivery Month]={'"$(=vMaxDateMonth+1)"},[Delivery Year]={'$(vMaxDateYear)'},

Date = {'<=$(=vMaxDate)'}>}OrderQuantity)

I have also tried using rangesum but I have not gotten it to work in any meaningful way.

Thank you

Jacob

[petter]

With the limited sample data this seems to work ... hacked together a bit quickly without much testing:

[Anonymous]

Petter, I am not able to get into the file you have shared to see the formula.

Are you able to explain?

[petter]

I have created a straight table:

Dimensions:

[Delivery Month] 

Expressions:

Max([Date Year])        with label Date Year

Min([Date Month])      with label Date Month

Sum({<[Business Days Remaining in Month]=>} [Order Quantity])     with label Orders

Min([Business Days Remaining in Month])     with label Business Days left

You can drag and drop the resulting columns so you get them in the right position.

I have created a list box so you can select which "Business Days Remaining in Month" you want for the two months.