cancel
Showing results for
Did you mean:
Frequent Visitor

## Rolling Sum for Dax Measure

Hi,

I have the below formulas:

EOY Comparative =
Var selectionMonth = SELECTEDVALUE('Calendar Date To'[FiscalMonthName])
Var selectionMonthNumber = SWITCH(UPPER(selectionMonth), "JAN", 1, "FEB", 2, "MAR", 3, "APR", 4, "MAY", 5, "JUN", 6, "JUL", 7, "AUG", 8, "SEP", 9, "OCT", 10, "NOV", 11, "DEC", 12, BLANK())
Var selectionYear = IF (selectionMonthNumber >=7,
YEAR(SELECTEDVALUE('Calendar Date To'[Fiscal Year]))-1,
YEAR(SELECTEDVALUE('Calendar Date To'[Fiscal Year])))
Var slicerYYMM = selectionYear& "-" & Format(selectionMonthNumber,"00")
var selYear = Max(RawData[FP])
Var Outcome = CALCULATE(IF(
DATEVALUE(selYear) <= DATEVALUE(SlicerYYMM),
[SumAct Formatted MTD],
[EOY Month Value]
), YEAR('Calendar'[Fiscal Year]) = YEAR(SELECTEDVALUE('Calendar Date To'[Fiscal Year])))
Return
Outcome

EOY Month Value = ([YTD SUM EOY])/ ([EOY Cummalative Days] - [EOY Number of Days in Month]) * [EOY Number of Days in Month]

YTD SUM EOY =
Var selectionMonth = SELECTEDVALUE('Calendar Date To'[FiscalMonthName])
Var selectionMonthNumber = SWITCH(UPPER(selectionMonth), "JAN", 1, "FEB", 2, "MAR", 3, "APR", 4, "MAY", 5, "JUN", 6, "JUL", 7, "AUG", 8, "SEP", 9, "OCT", 10, "NOV", 11, "DEC", 12, BLANK())
Var selectionYear = IF (selectionMonthNumber >=7,
YEAR(SELECTEDVALUE('Calendar Date To'[Fiscal Year]))-1,
YEAR(SELECTEDVALUE('Calendar Date To'[Fiscal Year])))
Var slicerYYMM = selectionYear& "-" & Format(selectionMonthNumber,"00")
Return
CALCULATE([SumAct Formatted MTD], ALL('Calendar'[Date]), 'Calendar'[Date] >= DATEVALUE(slicerYYMM) && 'Calendar'[Date]<= DATEVALUE(slicerYYMM))

 Slicers Month Name July Fiscal Year 2022 No of Days 31 31 30 31 30 31 31 28 31 30 31 30 Rolling Days Sum 31 62 92 123 153 184 215 243 274 304 335 365 Actual Forecast Forecast Forecast Forecast Forecast Forecast Forecast Forecast Forecast Forecast Forecast July August September October November December January February March April May June Amount 100 100 96.774194 100 96.77419 100 100 90.3226 100 96.77419 100 96.77419

The highlighted in brown is my expected output. My actual values return as expected but I am not aware on how to consider the rolling forecast values in the EOY Months Value formula.

Examples for output:

September should take the YTD sum (only July due to slicer selection) and take its previous EOY months value August to calculate its own value.

October should take the YTD sum (only July due to slicer selection) and take its previous EOY months value August and September to calculate its own value.

Hope this explains the issue in getting the rolling sum for EOY months value

Thanks,
Balreen
0 REPLIES 0

Announcements

#### Power BI Community Changes

Check out the changes to the Power BI Community announced at Build.

#### Power BI May 2023 Update

Find out more about the May 2023 update.