Cumulative over Cumulative issue

[Antmkjr]

https://drive.google.com/file/d/1pWvFXrZcE9s8QAq9dBjkaBSEUlJMJCRD/view?usp=sharing

I am trying to get the cumulative of EW , and it is not working.

I am using the below formula:

Cumulative EW =

CALCULATE([EW], FILTER (

ALLSELECTED ( 'Date' ),

'Date'[Date] <= MAX ( 'Date'[Date] ) ))

I understand there is some context that i have to break, ths can be done by creating a calculated table and then a measure as below:

BreakContextTransitionTable = 
VAR __YMTable = SUMMARIZE(ALL('Date'), 'Date'[MonthnYear])
VAR __YMTableEW = ADDCOLUMNS(__YMTable, "@EW", [EW])
RETURN
__YMTableEW

EW = 
VAR __filter = FILTER(BreakContextTransitionTable, BreakContextTransitionTable[MonthnYear] <= MAX('Date'[MonthnYear]))
RETURN
SUMX(__filter, BreakContextTransitionTable[@EW])

But can anyone let me know, how can this be done in a single measure , without creating a new calculated table.

[lbendlin]

What is your expected outcome? Do you want to add up all the EW values for the current and prior months?

[Antmkjr]

Expected result:

[Antmkjr]

@lbendlin @amitchandak @mahoneypat 

any help on this is highly appreciated

[Nese_FZ]

Hi Anu

I have the same problem... Has anyone helped?

[mahoneypat]

Please try this measure expression.  It is a little slow but that may be because of your existing measures that it references.

NewMeasure =
VAR vMaxDate =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        SUMX (
            VALUES ( 'Date'[MonthInCalendar] ),
            [EW]
        ),
        FILTER (
            ALL ( 'Date'[Date] ),
            'Date'[Date] <= vMaxDate
        )
    )

Pat

[Antmkjr]

Thanks @mahoneypat , but this measure seems to be extremely slow even with small data.

And my actual model is very huge 😞

Any ways to improve performance?

[mahoneypat]

Here is a new measure that avoid referencing your other measures.  I ran out of time before work, but it is very close and much faster.  Hopefully, you can tweak it from here.  If not, I can finish it this evening.

NewMeasure3 =
VAR vMaxDate =
    MAX ( 'Date'[Date] )
VAR vMonths =
    CALCULATETABLE (
        DISTINCT ( 'Date'[MonthInCalendar] ),
        FILTER (
            ALL ( 'Date'[Date] ),
            'Date'[Date] <= vMaxDate
        )
    )
VAR vSummary =
    ADDCOLUMNS (
        vMonths,
        "cResult",
            VAR vODCumulative =
                CALCULATE (
                    SUM ( Cashflow[Amount] ),
                    Cashflow[Type] = "FC",
                    FILTER (
                        ALL ( 'Date'[Date] ),
                        'Date'[Date] <= vMaxDate
                    )
                )
            VAR vCashCost =
                CALCULATE (
                    SUM ( CashCost[Cash Cost] )
                )
            VAR vResult = vODCumulative - vCashCost
            RETURN
                vResult
    )
RETURN
    SUMX (
        FILTER (
            vSummary,
            [cResult] > 0
        ),
        [cResult]
    )

Pat

[mahoneypat]

@Antmkjr  Finally got back to this one.  Here is the final measure that works and is much faster.

NewMeasure3 =
VAR vMaxDate =
    MAX ( 'Date'[Date] )
VAR vMonths =
    CALCULATETABLE (
        SUMMARIZE (
            'Date',
            'Date'[MonthInCalendar],
            "cThisMaxDate", MAX ( 'Date'[Date] )
        ),
        FILTER (
            ALL ( 'Date'[Date] ),
            'Date'[Date] <= vMaxDate
        )
    )
VAR vSummary =
    ADDCOLUMNS (
        vMonths,
        "cResult",
            VAR vODCumulative =
                CALCULATE (
                    SUM ( Cashflow[Amount] ),
                    Cashflow[Type] = "FC",
                    FILTER (
                        ALL ( 'Date'[Date] ),
                        'Date'[Date] <= [cThisMaxDate]
                    )
                )
            VAR vCashCost =
                CALCULATE (
                    SUM ( CashCost[Cash Cost] )
                )
            VAR vResult = vODCumulative - vCashCost
            RETURN
                vResult
    )
RETURN
    SUMX (
        FILTER (
            vSummary,
            [cResult] > 0
        ),
        [cResult]
    )

Pat

[Antmkjr]

Brilliant you are!

[stevedep]

@Antmkjr , Can you add some sample data & expected outcome. I am unable to download files. We can fix this for you. 

[Antmkjr]

https://drive.google.com/file/d/1pWvFXrZcE9s8QAq9dBjkaBSEUlJMJCRD/view?usp=sharing

Could you please check if you are able to download this?

Expected Outcome: