Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I am trying to create a matrix that forecasts my future cash balance. The starting point for it is the prior day actual cash balance and it is updated each day. I've created a measure to store the prior day balance.
I can't figure out how to add the running total column to the measure containing the prior day balance to get the PROJECTED CASH column. No matter what I do it doesn't add up correctly.
Does anyone know how to accomplish this? do I need to build it differently.
Below is my expected output
Prior Day Closing Balance = '9533 Closing Balance'[9533 Closing Balance] + 'DDA''s'[DDA's]
''' Prior Day Closing Balance as of 4/7 = 600 '''
Running Total = CALCULATE ( [Total Expenses] , FILTER(ALLSELECTED(Dates), Dates[Date] <=MAX(Dates[Date]) && Dates[Date] >= TODAY()) )
Date | Expenses a | Expenses b | Total Expenses | Running Total | Projected Cash |
4/8/2022 | -50 | -100 | -150 | -150 | 450 |
4/9/2022 | -20 | -30 | -50 | -200 | 400 |
4/10/2022 | -30 | -50 | -80 | -280 | 320 |
Hi @erg2494 ,
You could try
Running Total = SUMX( FILTER(ALLSELECTED(Dates), Dates[Date] <=MAX(Dates[Date]) && Dates[Date] >= TODAY()), [Total Expenses] )
Or
Running Total = SUMX( FILTER(ALLSELECTED(Dates), Dates[Date] <=MAX(Dates[Date]) ), [Total Expenses] )
If there are still errors, please share your [Total Expenses] formula.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello, the running total formula works. But now how do I ADD into it my prior day balance measure? which will always have a date of 1 business day prior to today().
in other words, how do i simply add a measure into the running total column? keep in my mind that the prior day balance is the starting point for my running total column.
i
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |