Rolling sum not from zero

[paolomint]

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

[Fowmy]

@paolomint 

Not very clear about the requirement.

Can you check this, Repace the XXXXX with the filed you need to net off

ROLLING 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 🙂

YouTube  LinkedIn

[paolomint]

Hello @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.

[paolomint]

@Ashish_Mathur

thanks for your reply, 

it was obvius, anyhow to avoid any misunerstanding, I modify the post.

Now the data are showed in the correct way, can you help me?

Thank you very much

Paolo 

[V-lianl-msft · Accepted solution]

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 ()
    )

Sample .pbix 

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

[Ashish_Mathur]

Hi,

I still do not understand.  The Opening Balance has to as of a certain date.  So it should be a part of Table1 (first row) with a date assigned to 1000.