Calculate Cumulative Amount for Individual ID and Pickup the date at which amount exceeded the targe

[ShivPB]

Hello ,

I have query regarding cumulative sum.

My Problem Statement is : I have table in which I have Account Number , Overdue Date, Total Amount and many such columns. Same account number have multiple entries which have different overdue date or have another value for other columns. So I want to derive such a date whose occured cumulative sum is greater than 1,00,000. 

For eg:

Account NO	Overdue Date	Amount	Cumulative Sum
AY00011	8/3/2022	10000	10000
AY00011	10/4/2022	50000	60000
AY00011	28/5/2022	50000	110000

In above table, I have account No : AY00011, which have different overdue dates and amounts. There is another column I need to calculate which is cumulative sum. Please share the logic for the same. And from that cumultaive sum I need to pickup the overdue date where cumulative sum for respective account is greater than 1 Lakh. In above case Cumulative Sum occurred greater than 1 Lakh at third row means we will pickup the date as "28/5/2022". 

It will be great help , if anyone share any ideas to solve the same.

[v-yiruan-msft · Accepted solution]

Hi @ShivPB ,

I created a sample pbix file(see attachment), please check whether that is what you want.

Flag = 
VAR _target = 100000
VAR _tab =
    ADDCOLUMNS (
        'Table',
        "@culvalue",
            CALCULATE (
                SUM ( 'Table'[Amount] ),
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Account NO] = EARLIER ( 'Table'[Account NO] )
                        && 'Table'[Overdue Date] <= EARLIER ( 'Table'[Overdue Date] )
                )
            )
    )
VAR _mindate =
    MINX ( FILTER ( _tab, [@culvalue] > _target ), [Overdue Date] )
RETURN
    IF ( SELECTEDVALUE ( 'Table'[Overdue Date] ) = _mindate, 1, 0 )

Then apply visual-level filter with the condition(Flag is 1) as below screenshot:

Best Regards

[v-yiruan-msft · Accepted solution]

Hi @ShivPB ,

I created a sample pbix file(see attachment), please check whether that is what you want.

Flag = 
VAR _target = 100000
VAR _tab =
    ADDCOLUMNS (
        'Table',
        "@culvalue",
            CALCULATE (
                SUM ( 'Table'[Amount] ),
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Account NO] = EARLIER ( 'Table'[Account NO] )
                        && 'Table'[Overdue Date] <= EARLIER ( 'Table'[Overdue Date] )
                )
            )
    )
VAR _mindate =
    MINX ( FILTER ( _tab, [@culvalue] > _target ), [Overdue Date] )
RETURN
    IF ( SELECTEDVALUE ( 'Table'[Overdue Date] ) = _mindate, 1, 0 )

Then apply visual-level filter with the condition(Flag is 1) as below screenshot:

Best Regards

[ShivPB · Accepted solution]

Thank you so much for the solution