How to calculate conditional cumulative figures

[MrFlox]

Hello everyone, 

I am relatively new to powerbi (not entirely new to programming itself) and have come upon a challenge. I need to recreate the below variable Z on BI. The whole difficulty lies in creating a cumulative figure (Y) that depends on whether the "X" went from zero to a non-zero. The explanation is simple:

Z is calculated as X - cumulative Y. However, if X went to 0 in the previous date, and now is at non-zero, then the cumulative process of Y starts again at this precise date. You can see it in the highlighted rows which signal the refresh of the cumulative process.

The Date Rank Variable is simply the ranking of the leftside dates (it alligns to the left always so it looks stuck to the dates, don't get confused).

Date	Date Rank	X	Y	Z = (X - cumulative Y) OR X-Y if refreshed
01/09/2018	1	30	56	-26
30/09/2018	2	40	4	-20
31/10/2018	3	0	64	-124
30/11/2018	4	12	4	8
31/12/2018	5	36	6	26
31/01/2019	6	57	64	-17
28/02/2019	7	86	0	12
31/03/2019	8	0	574	-648
30/04/2019	9	0	7	-655
31/05/2019	10	1	5	-4
30/06/2019	11	4	2	-3
31/07/2019	12	8	0	1
31/08/2019	13	7	45	-45
30/09/2019	14	0	47	-99
31/10/2019	15	765	2	763
30/11/2019	16	567	7	558
31/12/2019	17	47	4	34
31/01/2020	18	8	0	-5
29/02/2020	19	4	484	-493
31/03/2020	20	0	0	-497
30/04/2020	21	0	5	-502
31/05/2020	22	0	8	-510
30/06/2020	23	3	6	-3
31/07/2020	24	4	64	-66
31/08/2020	25	2	6	-74
30/09/2020	26	0	8	-84

!!I have an extra complexity that I should have mentioned, the table above is for a single ID number. In reality there are multiple ID numbers in that data, thus the above calculations should work for each ID number separatelly. The ID number is like an extra column.

I have spent hours on it...can anyone help with the function needed to calculate Y?

Many thanks!

[v-eqin-msft · Accepted solution]

Hi @MrFlox ,

Please try :

Flag(X<>0) Column = 
var _last0=CALCULATE(MAX('Table'[Date Rank]),FILTER('Table','Table'[Date Rank]<EARLIER('Table'[Date Rank]) && 'Table'[X]=0 && 'Table'[ID]=EARLIER('Table'[ID])))+0
return 
IF('Table'[X]=0,BLANK(),_last0)

FillFlag Column = MAXX(FILTER('Table','Table'[Date Rank]<=EARLIER('Table'[Date Rank]) && 'Table'[ID]=EARLIER('Table'[ID])),[Flag(X<>0) Column])

Z Column = [X]- CALCULATE(SUM('Table'[Y]),FILTER('Table','Table'[Date Rank]<=EARLIER('Table'[Date Rank]) && [FillFlag Column]=EARLIER('Table'[FillFlag Column]) &&'Table'[ID]=EARLIER('Table'[ID]) ) )

The final output:

If you just want to extract some records, you could apply some filters. 

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

[v-eqin-msft]

Hi @MrFlox ,

Please add  the following formual to each of the measure I created before:

&& 'Table'[ID]=MAX('Table'[ID])

The final output is shown below:

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

[MrFlox]

Thank you Eyelyn for the solution! I am very close. I input the formulas and get the same results when compiling the table in the report view.

However, because I will be extracting the data (and the data is millions of rows long), I need the extracted to be in my Data View, as columns: i.e Date, Date Rank, ID, X, Y, Flag(X<>0), FillFlag, Z

I have tried to employ all the formulas as columns, but failed. I believe some modifications is needed. Could you advise?

Thank you in any case!

[v-eqin-msft]

Hi @MrFlox ,

Please follow these steps:

1. Find the latest [Date Rank] value with X=0 for all records where X<> 0:

Flag(X<>0) =
VAR _last0 =
    CALCULATE (
        MAX ( 'Table'[Date Rank] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date Rank] < MAX ( 'Table'[Date Rank] )
                && 'Table'[X] = 0
        )
    ) + 0
RETURN
    IF ( MAX ( 'Table'[X] ) = 0, BLANK (), _last0 )

2.Fill in the [Flag(X<>0)] value of the previous row where it is blank:

FillFlag =
MAXX (
    FILTER ( ALL ( 'Table' ), 'Table'[Date Rank] <= MAX ( 'Table'[Date Rank] ) ),
    [Flag(X<>0)]
)

3.Now use the following formula to sum of [Y] based on the [Date] and the same [FillFlag] 

Z =
MAX ( 'Table'[X] )
    - CALCULATE (
        SUM ( 'Table'[Y] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date Rank] <= MAX ( 'Table'[Date Rank] )
                && [FillFlag] = MAXX ( 'Table', [FillFlag] )
        )
    )

The final output is shown below:

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

[MrFlox]

Thank you Eyelyn9, I have an extra complexity that I should have mentioned, the table above is for a single ID number. In reality there are multiple ID numbers in that data, thus the above calculations should work for each ID number separatelly. The ID number is like an extra column.

How can I apply your formulas for a data with multiple ID numbers?

Also, why do you put the '+ 0' part after Calculate, in the 1st step? 

Many thanks, and sorry for the inconvenience!

[Jihwan_Kim]

Hi, @MrFlox 

Please check the below picture and the sample pbix file's link down below.

All measures are in the sample pbix file.

I believe you can easily follow the steps.

https://www.dropbox.com/s/j4mq93jqzwlgdek/flox.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/

[amitchandak]

@MrFlox , Try a new measure like

calculate(sum(Table[X]) -Sum(Table[Y]), filter(allselected(Table), Table[Date] <=Max(Table[Date])))