Rolling Sum by Month by Year

[intern56]

I have monthly order data over many fiscal years (the fiscal year starts April 1 and ends May 31). I am having trouble creating a rolling sum of the order data by month by year. I have read about various other approaches but none of them seem to work for me. An example of what I would like is below:

                         | Year 1 | Year 2 |

April                       1           0

May                        1           1

June                        2          2

July                        0           0

August                   5           3

September            1            0

October                 1           1

November             0            2

December             2            3

January                 4             1

February               1             1

March                   1             1

Total                    19           15

Would Become:

                         | Year 1 | Year 2 |

April                       1           0

May                        2           1

June                       4           3

July                        4           3

August                   9           6

September            10          6

October                 11          7

November             11          9

December             13          12

January                 17          13

February               18           14

March                   19          15

Total                    19           15

I created a Calendar Table with Date, DateYear, and DateMonth columns.

My Data Table has Order Qty, Order Date, and FY (Fiscal year) columns.

If someone could help me create this measure and then explain what it was doing, it would be great!

[Anonymous · Accepted solution]

Hi @intern56,

You can try to use below formula to calculate the rolling total:

Steps:
1. Add calculated column to convert month to date.

Date = DATEVALUE([Month]&"/"&2017) 

2. Write rolloinh total measure based on date column.

rolling year 1 = SUMX(FILTER(ALLSELECTED('sample'),[Date] <=MAX([Date])),[Year 1])

rolling year 2 = SUMX(FILTER(ALLSELECTED('sample'),[Date] <=MAX([Date])),[Year 2])

Regards,

Xiaoxin Sheng

[intern56]

Cool it works thanks