Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Cumulative over Cumulative issue

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.

11 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    5 years ago

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

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        mahoneypat
        Icon for Microsoft Employee rankMicrosoft Employee
        5 years ago

        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

  • stevedep's avatar
    stevedep
    Icon for Memorable Member rankMemorable Member
    5 years ago

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