Projected Cash Balance based on dynamic historical average

[Anonymous]

Expert Community,

I need some expert advice on how to predict a cash balance based on ''fixed" monthly projected average using historical data in PBI. Please find below table an illustration of the structure (consolidated and built in Excel). 

Actual data: Cash Flow data is based on General Ledger Entries

Situation: This period have 9 (X) months of aggregated historical reported numbers related to cash expenditure (Outflow). The outflow is subtracted from the starting cash balance each month. The projected average cash flow is dynamically calculated and updated based on realised numbers (X)

Complication: How to construct a DAX measure that applies the historical average as a fixed projected average to predict the future cash balance?

Please state if you need additional information. Thank you. 

Best regards,

Henrik

[v-deddai1-msft]

Hi @Anonymous ,

Would you please show us some sample data and expected output with details? I don't get your logic for calculation well based on your description. Is the rolling average you want? If so, please refer to  https://www.mssqltips.com/sqlservertip/5635/creating-a-rolling-average-graph-with-power-bi/

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

Best Regards,

Dedmon Dai

[Anonymous]

Hi @v-deddai1-msft ,

Thank you for replying to my question. 

Please find below a consolidated screedump of the two important steps. As the forum does not support upload of xlsx-format, I can forward the sample data via e-mail. I appreciate your input, unfortunately it is only a part of the solution. 

The "Projected average cash outflow" is based on the rolling average. The issue I am facing is how apply the average (rolling average) as a fixed value in future N periods which will provide be with a 'future' Cash Balance (marked in green). 

I can add that I have tried to create a "Forecasting formula" using DAX, however as the formula is dynamic into the future it has limited utility. The formula was made as variables using past 12 month, for instance: 
VAR CF_1Mago = Calculate([Cash Outflow];DATEADD(DimDate[Date];-1;MONTH))

The Cash balance equation would be: F(x) = 1.000.000 + Cash Outflow_t + Projected (rolling) average cash outflow_t,

Best regards,
Henrik Hoffmann

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

You can add a index column in your raw table:

Then use the following two measure:

Projescted Average Cash Outflow = IF(ISBLANK(MAX('Table'[Actual Cash Balance])),AVERAGEX(FILTER(ALL('Table'),NOT(ISBLANK('Table'[Outflow]))),'Table'[Outflow]),BLANK())

Cash Balance = IF(ISBLANK(MAX('Table'[Outflow])),CALCULATE(FIRSTNONBLANK('Table'[Actual Cash Balance],1),ALL('Table'))+SUMX(FILTER(ALL('Table'),'Table'[Index]<=MAX('Table'[Index])),[Projescted Average Cash Outflow]),MAX('Table'[Actual Cash Balance]))

For more details, please refer to the pbix file:https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/Ed3rn4lZMJ9El3ebxyhhJf4BNa4DQdpJ2Don766DUpaHcA?e=TumfV0

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

Best Regards,

Dedmon Dai

[Anonymous]

Hi Dedmon,

Thank you for your solution which is acceptable, if my dataset was identifical to your Power BI data.
However, my dataset is somewhat more complicated then originally described (I tried to simplify it). 

The dataset is based on General Ledger Entries with daily transactions. This means that I cannot apply the average X function, as it takes the daily average, unless it can be adjusted perhaps nested in a SUMX function to obtain the monthly average. The last posting date can be assumed to be "TODAY". 

I have attached the basics of data model to assist in finding the solution. 

Br

Henrik