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