Budget Forecasting based on lookbacks

Hi Anonymous ,

1. According to your description and expected output, I have created a data sample for test.

2. And based on this——If we assume the current day is 3/19/2022, and we want to forecast spend projections using a 3 day lookback, we'd pull daily budget data from 3/16/2022. 

I create two additional two tables for slicers:

Date Slicer = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date])) 

Lookbacks = ADDCOLUMNS( {"3 Day Lookback","5 Day Lookback","7 Day Lookback"} ,"Number",CONVERT( LEFT([Value],1),INTEGER))

3.Then please firstly create a flag measure to filter needed date, and apply it to visual-filter pane, set as "is 1"

Filter Flag = IF(MAX('Table'[Date])>= SELECTEDVALUE('Date Slicer'[Date])-MAX('Lookbacks'[Number]),1,0) 

4. Now create measures:

Daily Actual Spend Measure = CALCULATE(SUM('Table'[Daily Actial Spend]),FILTER('Table',[Date]>=SELECTEDVALUE('Date Slicer'[Date])-MAX('Lookbacks'[Number]) && [Date]<SELECTEDVALUE('Date Slicer'[Date])))

Cumulative Budget = 
var _sele=SELECTEDVALUE('Date Slicer'[Date])-MAX('Lookbacks'[Number]) // selected date - X day lookback
var _sumofbefore= CALCULATE(SUM('Table'[Daily Budget]),FILTER(ALL('Table'),[Date]<_sele ))
return SWITCH(TRUE(), MAX('Table'[Date])=_sele,_sumofbefore, MAX('Table'[Date])>_sele, CALCULATE(SUM('Table'[Daily Budget]),FILTER(ALL('Table'),[Date]<=MAX('Table'[Date])  && [Date]>_sele)) +_sumofbefore)

Cumulative Projected Spend = 
var _sele=SELECTEDVALUE('Date Slicer'[Date])-MAX('Lookbacks'[Number]) // selected date - X day lookback
var _sumofbefore= CALCULATE(SUM('Table'[Daily Actial Spend]),FILTER(ALL('Table'),[Date]<_sele ))
return SWITCH(TRUE(), MAX('Table'[Date])=_sele,_sumofbefore, MAX('Table'[Date])>_sele, _sumofbefore + DATEDIFF(_sele,MAX('Table'[Date]),DAY) *CALCULATE(AVERAGE('Table'[Daily Actial Spend]),FILTER(ALL('Table'),[Date]<_sele+3&& [Date]>=_sele)))

Pct Diff(%) = ( [Cumulative Projected Spend] -[Cumulative Budget]) / [Cumulative Budget]

Final output:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Anonymous ,

So do you mean the cumulative columns should sum values for the same year-month? And the visual should only show selected year-month value?

You may add more filters to the DAX syntax.For example:

&& YEAR(SELECTEDVALUE('Date Slicer'[Date]))= YEAR(MAX('Table'[Date])) && MONTH(SELECTEDVALUE('Date Slicer'[Date]))=MONTH(MAX('Table'[Date]))

Filter Flag = IF(MAX('Table'[Date])>= SELECTEDVALUE('Date Slicer'[Date])-MAX('Lookbacks'[Number]) && YEAR(SELECTEDVALUE('Date Slicer'[Date]))= YEAR(MAX('Table'[Date])) && MONTH(SELECTEDVALUE('Date Slicer'[Date]))=MONTH(MAX('Table'[Date])),1,0) 

Cumulative Budget = 
var _sele=SELECTEDVALUE('Date Slicer'[Date])-MAX('Lookbacks'[Number]) // selected date - X day lookback
var _sumofbefore= CALCULATE(SUM('Table'[Daily Budget]),FILTER(ALL('Table'),[Date]<_sele && YEAR([Date])=YEAR(_sele) && MONTH([Date])=MONTH(_sele)))

return SWITCH(TRUE(), MAX('Table'[Date])=_sele,_sumofbefore, MAX('Table'[Date])>_sele && YEAR(_sele)= YEAR(MAX('Table'[Date])) && MONTH(_sele)=MONTH(MAX('Table'[Date]))  , CALCULATE(SUM('Table'[Daily Budget]),FILTER(ALL('Table'),[Date]<=MAX('Table'[Date])  && [Date]>_sele)) +_sumofbefore)

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.