Computing daily Cash Closing Balance & forecast using DAX

[Aldek_U]

Hello Everyone,

Last 2 weeks I’m fighting with a problem how to calculate a forecasted cash closing balance based on actuals & daily / weekly cashflows forecasted for the “to go” days/weeks.

The key purpose of this is to monitor cashbalance and predict if not run out. In the near future I would also like to add two slicers each with ratio from 50% to 150% and multiply forecaste outflows and inflows respectively by the selected ratio -> it would give me a kind of sensitivity analys saying if we underperform in cashinflows to 90% of forecast/plan and overperform in expense to 110% of what was expected the closing cash balance would be X.

My date is:

1. Fact Table with Actual daily transactions - it comes from a bank csv generated file with all the transactions. What may be important here, it also includes cash closing balance as at 31 Dec 2020.
2. Fact table with Budgeted daily cash flow - originally it was monthly typical budget but somehow I transfromed it into expected daily cash out- and inflows.
3. DateTable

The expected outcome would reflect the logic:

if the selected data range is today or earlier provide the actual cash balance as at the date However if the selected data range is in the future, the measure would return forecasted cash balance as at the future day computed like:
[CashClosingBalance as at MaxActualDate] + Cummulated CashFlows From MaxActualDate to the SelectedDate.

As my model is right now full of detailed actual and confidential data I prepared a fantom data here.

So, let say I started a year with CashBalance of 1000. The Actuals are as at 10 Jan 2021 and after all the transactions from the begining of the year my cash balance is 923. Then my plan for the next days are spend 354 (from 11 to 31 Jan) and earn 591. It means my forecasted month closing balance is 923 - 354 + 591 = 1160.

The issues I faced and would love to be eliminated are:

1. CashClosingBalance no value as at 3/01 when no Actual CashFlows - it should repeat value from previous available day
2. No CashClosingBalance for future dates i.e. from 11 Jan
3. #2 results in not correct chart
4. ClosingBalance of 2000 as at 31Dec2020

CashClosingBalance.xlsx (22.7 KB) fantom data.pbix (90.3 KB)

I was trying to Summarize / Union both fact tables somehow but also failed.

[BrianJ]

@Aldek_U,

Thanks very much for the detailed description of the problem, the masked PBIX and the underlying data - all of that makes it much easier for us to provide quality support. I’m looping in Enterprise DNA Expert @Harsh, who is a wiz with these types of problems.

• Brian

[Harsh]

Hello @Aldek_U,

Thank You for posting your query onto the Forum.

Based on the issues that you’ve specified for elimination here’s the solution provided below accordingly -

1). Actual Net Cash Flows -

In the post, you’ve specified that you want to evaluate the results of actual till last date of the actual data. So below is the measure provided for the reference -

Actuals - Harsh = 
VAR _Cash_Opening_Balance = 
CALCULATE( 
    SUMX( 
        Actuals_from_bank , 
        Actuals_from_bank[Value] ) , 
    Actuals_from_bank[Transaction Details] = "CashClosing Balance" )

VAR _Actual_Inflows = 
CALCULATE(
    SUMX(
        Actuals_from_bank , 
        Actuals_from_bank[Value] ) , 
    Actuals_from_bank[Transaction Details] = "Sales" )

VAR _Actual_Outflows = 
CALCULATE(
    SUMX(
        Actuals_from_bank , 
        Actuals_from_bank[Value] ) , 
    Actuals_from_bank[Transaction Details] = "Purchase" )

VAR _Net_Cash_Flows = 
_Cash_Opening_Balance + _Actual_Inflows + _Actual_Outflows

RETURN
_Net_Cash_Flows + 0

2). Budgeted Net Cash Flows

Now, once you evaluate the results of the actual. Next step you want is to evaluate the results of the Budgeted data. Since you want the results of the Budgted Cash Flows after the current date i.e. After Today. Below is the measure provided for the reference -

Budgets - Harsh = 
VAR _Budgeted_Inflows = 
CALCULATE(
    SUMX(
        Budget , 
        Budget[Value] ) , 
    Budget[Transaction Details] = "Sales" )

VAR _Budgeted_Outflows =
CALCULATE(
    SUMX(
        Budget , 
        Budget[Value] ) , 
    Budget[Transaction Details] = "Purchase" )

VAR _Net_Cash_Flows = 
_Budgeted_Inflows + _Budgeted_Outflows

RETURN
IF( SELECTEDVALUE( DateTable[Date] ) > TODAY() , 
    _Net_Cash_Flows , 
    0 )

3). Fixing Totals Of Budgeted Net Cash Flows -

Once you write this measure for “Budgeted Net Cash Flows” you’ll observe that grand totals are not correct. So to fix the totals. Just small measure to fix it.

Budgets - Harsh - Totals = 
SUMX(
    SUMMARIZE(
        Budget , 
        Budget[Date] , 
        "@Totals" , 
        [Budgets - Harsh] ) , 
    [@Totals]
)

Note: One of our expert @Greg had already created a post on this specifically which covers everything pertaining to this type of issue. Below is the link of that post provided for the reference.

4). Total Net Cash Flows i.e. Actuals + Budgeted -

Now, to calculate the net cash flows which combines “Actuals Net Cash Flows” till last date (+) “Budgeted Net Cash Flows” which starts after the current date. Below is the formula provided for the reference -

Net Cash Flows - Harsh = [Actuals - Harsh] + [Budgets - Harsh - Totals]

5). Cumulative Net Cash Cash Flows -

Lastly, to calculate the cumulative cash flows closing balance as on last date. Below is the measure provided for the reference -

Cumulative Cash Flows = 
CALCULATE( [Net Cash Flows - Harsh] , 
    FILTER( ALLSELECTED( DateTable ) , 
        DateTable[Date] <= MAX( DateTable[Date] ) ) )

So here’s the screenshot of the final output provided below -

I’m also attaching the working of the PBIX file for the reference.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Note:

1). Since you had mentioned that actuals will be considered till last date i.e. today and thereafter budgets will be considered. So to make the calculations accordingly, I’ve changed the opening balance date from 31st December, 2020 to 30th April, 2021. And have considered the date in the data from 1st May, 2021 to 31st May, 2021 rather than from 1st January, 2021 to 31st January, 2021.

2). I’ve also added few data points into the Actuals data to check when there’s no data it consider’s the previous day’s data.

3). Wherever there’s no data, there 0 has been placed. So after 17th May, 2021 since you there’s no actual data it’ll showcase as 0. Also for Budgeted data before current date i.e. today it’ll showcase as 0. And based on this, you’ll have previous day’s data under the cumulative measure wherever there’s no previous day’s data. Otherwise it’ll skip dates and will directly jump onto the next date wherever you’ve the data.

Thanks and Warm Regards,
Harsh

CashClosingBalance - Harsh.xlsx (22.4 KB)

fantom data - Harsh.pbix (83.5 KB)

Fix Incorrect Totals

When using a measure column in a visual, it is not uncommon in Power BI to find that the totals are incorrect. The Fix Incorrect Totals DAX pattern can be used to correct this issue. When a DAX measure is providing the correct detail value it often, unfortunately, provides an incorrect value for the total row. This is due to there being no evaluation context for the total row. For example, when comparing the [Total Sales] to the [Sales LY] to find the minimum for a customer using the simple DAX…

[BrianJ]

@Harsh,

Wow! Amazing solution. Apparently, when I said you were “a wiz with these types of problems”, that was a massive understatement.

• Brian

[Harsh]

Hello @BrianJ,

Thank You so much for your kind words of appreciation.

Thanks and Warm Regards,
Harsh

[EnterpriseDNA]

Thank you so much for that wonderful solution @Harsh!

Hi @Aldek_U, did the response provided above help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

[Aldek_U]

Dear @Harsh,
Much appreciate your great effort to give me and the whole community such an extensive solution.

I followed your idea and applied it to the fantom model. Unfortunatelly I still have some discrepancies. So refering to the fantom date file and your great formulas, I have three comments

Here I paste a screen of @Harsh solution

1. That is the biggest problem I have - in my data I cannot apply the logic saying if there is no data for a particular historical (!) date do not provide budget figures. Simply saying, it was e.g. Sunday when no transactions were made so it is okey it is zero. But in my formulas Dax is applying Budget value. You made some note comment with this respect but honestly I didn’t get where / how to apply this.

2. It is exectly what was expected !!

3. In your formula for Budget there is
   RETURN IF( SELECTEDVALUE( DateTable[Date] ) > TODAY() , _Net_Cash_Flows , 0 )

I modified it slightly adding extra variable
VAR Max_Actual_Date = MAX(Actuals_from_bank[Date])
and concluded with
RETURN IF( SELECTEDVALUE( DateTable[Date] ) > Max_Actual_Date , _Net_Cash_Flows , 0 )

BTW - it may not be for sure that we have the actuals on a daily basis refreshed so I would prefer not to use Today() formula.

So I finished with something like this:

I upload my file as well fantom data vol2.pbix (83.2 KB)

[Harsh]

Hello @Aldek_U,

I knowingly didn’t used the MAX( Actuals_from_bank[Date] ) because there’s a granularity mismatch between the “Actuals” table and “Budgets & Dates” table. The data for Actuals is not consistent on a daily basis whereas the Budgets have the data on a daily basis. So now if I use the “MAX()” function see how the context is getting evaulated at a back-end.

What you actually thought was it’ll consider 17th May, 2021 as a final date and before 17th May, 2021 it’ll consider all the dates in a consistent manner. But since “Actuals” table itself doesn’t have the data on a consistent or on daily basis it absolutely doesn’t makes sense to use the “MAX()” or “LASTDATE()” function. The reason is, in both the cases it’ll make head-to-head comparison i.e. Date from the Actual table will be compared with the Date from the Date table and wherever the data misses out it’ll starting giving you results as “BLANK()”.

And that’s why since there’s no data for “Actuals” on following dates - 2nd May, 2021, 11th May, 2021, 13th May, 2021, 14th May, 2021 and 16th May, 2021 it started to give vague results in the Budgets calculation. Actually for these dates the values should be considered as “0” since last transaction date in “Actuals” is on 17th May, 2021 but as mentioned earlier due to granularity issues it considered the actual values for the “Budgets”. Below is the screenshot provided for the reference -

And that’s the reason why I relied on “TODAY()” function.

So now, there’re 2 options -

1). Prepare the “Actual” table by inserting the dates wherever the data points are missing or not there and put values as 0 and than you can make use of MAX( Actuals_from_bank[Date] ).

2). Record and refresh the “Actual” table on a daily basis and use “TODAY()” function.

The reason why you need to fix the “Actual” table is because your Budgets calculation is dependant on the Actuals and unless granularity issue is not solved you’ll face this problem and will be required to stick with the “TODAY()” function.

Hoping this explanation useful and helps you why I didn’t used the “MAX()” or “LASTDATE()” function in the analysis.

Thanks and Warm Regards,
Harsh

[EnterpriseDNA]

Hi @Aldek_U, did the response provided by @Harsh help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

[Aldek_U]

@Harsh Again, much appreciate your comments and explanation. Now I understand the reason Today() is used for. And I will of course mark your comments as solution One more time thank you very much for your help.

I applied the solution to the real data. The actual data I have is as at 30 April. The Cash Closing Balance for that date is correct. If you look at the screen, there is no data for May and for past 21 May days the cash closing balance is not changing. It will change for today and later. What would be the best ever solution is to treat the budget numbers for missing past dates because e.g. the year end cash balance is underestimated if anything actual is missing.

At the moment I need to live with that and try to change the process of data supply somehow. But do you @Harsh or Anyone could have an idea how to fix this - maybe at PQ level (providing e.g. for missing days some fake data like 0.0001 for empty past days) ?

[Harsh]

Hello @Aldek_U,

You’re Welcome.

I’m glad that I was able to assist you in this query.

Yes, there’s a way out for this. Wherever the data is missingv i.e. let’s say in the example we didn’t had any data on 2nd May, 2021. So in this case just insert a date as 2nd May, 2021 in your actual data.

So my point is wherever the data points are missing just insert the dates there and put the values as 0. And then you’ll be fine to use the “MAX( Actual_from_bank[Date] )” or “LASTDATE( Actual_from_bank[Date] )” rather than using “TODAY()” function. I know that TODAY() is only used as a last resort and that too when the data is getting updated at a daily level.

It would have been really fine if you’re using the actuals for the analysis but what’s happening here is your budgets are dependent on the actuals and in your final result you’re considering actuals till last day and than budgets from the next day. So you’re using a combination of two under the one hood and that’s why I’m recommending to get the actuals data sorted and by that way your entire analysis will be sorted.

Hoping this helps you and a really interesting problem it was to work with you on this one.

Thanks and Warm Regards,
Harsh