Forum Discussion
Helper IIIRolling sum not from zero
Hi,
I'm workin on a running sum but this time I need something different:
As first step I built a standard rolling sum:
ROLLING SUM = CALCULATE(sum(SCADENZE[IMPORTO]), filter(ALLSELECTED(SCADENZE), SCADENZE[DATA SCADENZA]<=MAX(SCADENZE[DATA SCADENZA])))
It make a rolling sum of the positive/negative previsional variation in the bank account.
Now I need that the calculation not start from Zero but from the balance of the bank account
I made a measure to calculate the balance of the bank account at " time zero" and now I need to create a new measure to "say" to powerBi don't start from ZERO but from BANK_BALANCE
If I do this way:
= BANK_BALANCE + ROLLING SUM
It doesn't work, It make the calculation for each day. I need that Bank balance is considered only for the first day.
An example can help me to better explain:
| DATE | BANK BALANCE* | VARIATION | WRONG | WRONG | RIGHT |
| 25.09.20 | 1000 | +200 | 200 | 1200 | 1200 |
| 26.09.20 | 1000 | +400 | 600 | 1400 | 1600 |
| 30.09.20 | 1000 | -100 | 500 | 900 | 1500 |
| 02.10.20 | 1000 | -700 | -200 | 300 | 800 |
* Bank Balance doesn't change because we are talking about future variation still not happened.
Thanks for yor help
Hi paolomint ,
Maybe I understand what you mean.Based on your description, you have two tables, and you want the amount retained in the "bank" table as the starting amount of the "flow" table.
Try the measure.
ROLL_FLOW = VAR base = [ROLL_BANK] VAR end_date = CALCULATE ( MAX ( Bank[Date] ), ALL ( Bank ) ) RETURN IF ( MAX ( FLOW[Date] ) > end_date, CALCULATE ( SUM ( FLOW[VARIATION] ), FILTER ( ALLSELECTED ( FLOW ), FLOW[Date] <= MAX ( FLOW[Date] ) ) ) + base, BLANK () )Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
- Fowmy
Super User
paolomint
Not very clear about the requirement.
Can you check this, Repace the XXXXX with the filed you need to net offROLLING SUM = CALCULATE ( SUM ( SCADENZE[IMPORTO] ) - SUM ( SCADENZE[xxxxx] ), FILTER ( ALLSELECTED ( SCADENZE ), SCADENZE[DATA SCADENZA] <= MAX ( SCADENZE[DATA SCADENZA] ) ) )________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
paolomintHello @Fowmy
thank you for your quick response, it doesn't work,
Let me try to explain better with an example
Account balance transactions are in a table [BANK]
OPENING BALANCE: 1000
Date Variation 01.09.2020 +300 02.09.2020 -400 05.09.2020 +800 BALANCE FINAL : 1700
To get the final balance I can use a classic RUNNING SUM formula:
ROLL_BANK: CALCULATE(sum(BANK[VARIATION]), filter(ALLSELECTED(BANK), BANK[DATE]<-MAX(BANK[DATE])))
--------------------------------------------------------------------------------------------------------------------------------------
In another table I have the future varation [FLOW]
Date Variation 25.09.2020 +200 30.09.2020 -500 05.10.2020 - 400 ROLLING SUM - 700 RUNNING SUM Formula:
ROLL_FLOW: CALCULATE(sum(FLOW[VARIATION]), filter(ALLSELECTED(FLOW), FLOW[DATE]<-MAX(FLOW[DATE])))
---------------------------------------------------------------------------------------------------------------------------------------
I need a measure to get the following result:
Date Variation 25.09.2020 1900 30.09.2020 1400 05.10.2020 1000 Thank you for your help
Good bye
- Ashish_Mathur
Hi,
In the BANK Table, it does not make sense to have a text entry (Opening balance) in a Date column. There should be a Date in place of that text entry.
