Budget Forecasting based on lookbacks

[Anonymous]

Need to create monthly budget forecasts based on different lookbacks (3 day, 5 day, 7 day)

Looking for something such as the following:

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. 

First I need to create the cumulative budget column. On 3/16, the cumulative budget value should equal the sum of the budget from 3/1 to 3/15. Every day after 3/16, we cumulatively add the 'Daily Budget Actual' column values to get the cumulative budget total.

Second, I need to create the cumulative projected spend. This takes the average from the 'Daily Actual Spend' Column in the 3 day window prior to the current day, since we are using a 3 day lookback. Then it cumulatively adds it to the 'Cumulative Projected Spend' column from 3/16 to get the Cumulative Projected Spend. 

I need a Power BI table that is something like the format below, but am totally stumped. Help would be appreciated here. 

Thanks!

[v-eqin-msft · Accepted solution]

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.

[v-eqin-msft · Accepted solution]

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.

[v-eqin-msft · Accepted solution]

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.

[Anonymous]

Thank you for your reply, I appreciate the help! The only issue I have now is with the cumulative columns. Perhaps it is because the dataset includes data for several months prior to 3/19/2022 (starting from 2021) and months after 03/2022 (April).  with the code you have sent over, I'm only getting constant values from 3/17/2022 to 3/31/2022 

I need these tables to only include data for the month that is selected by the date slicer, and reset the next month. I was able to use some of the EOMONTH() functionality to deal with issues in other columns, but am stumped by the issues in the cumulative columns.

 

Let me know if this makes sense. Thanks!!