Forum Discussion
Solved
Rolling Sum by Month by Year
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 var...
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
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
Cool it works thanks