Computing daily Cash Closing Balance & forecast using DAX

DAX DAX Calculations
3

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 -

Final Results
Final Results1422×795 51.2 KB

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)

4 Likes