Forum Discussion

AllanBerces's avatar
AllanBerces
Post Prodigy
1 year ago
Solved

Cumulative Earned

Hi good day,

Can someone help me on my calulated column on my table below.

 

**bleep**.Earned =       VAR CurrentDate = 'Table1'[Date]
                              VAR CurrentSubTask = 'Table1'[Name]
                              VAR Filteredtable = FILTER('Table1','Table1'[Date]<=CurrentDate && 'Table1'[Name]= CurrentSubTask)

                              return

                              CALCULATE(SUM('Table1'[Weekly_Earned]), Filteredtable)

DESIRED OUTPUT

 

Thank you

Need Help
  • Irwan's avatar
    Irwan
    1 year ago

    hello AllanBerces 

     

    please check if this accomodate your need.

     

    i assume every different value inside the table always has 3 duplicates. if yes, then you can summarize it first before calculating cumulative.

    create a new calculated column with following DAX.

    Cumulative Earned =
    var _Sum = 
    SUMMARIZE(
        'Table',
        'Table'[Date],
        'Table'[Name],
        'Table'[WeeklyEarned]
    )
    Return
    SUMX(
        FILTER(
            _Sum,
            'Table'[Date]<=EARLIER('Table'[Date])&&
            'Table'[Name]=EARLIER('Table'[Name])
        ),
        'Table'[WeeklyEarned]
    )

     

    Hope this will help.

    Thank you.

5 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    1 year ago

    hi AllanBerces ,

     

    try like:

    Earned =       
    VAR CurrentDate = 'Table1'[Date]
    VAR CurrentSubTask = 'Table1'[Name]
    return
    SUMX(
    FILTER(
        'Table1',
        'Table1'[Date]<=CurrentDate 
        && 'Table1'[Name]= CurrentSubTask
    ),
    'Table1'[Weekly_Earned]
    )

  • Irwan's avatar
    Irwan
    Super User
    1 year ago

    hello AllanBerces 

     

    please check if this accomodate your need.

     

    i assume every different value inside the table always has 3 duplicates. if yes, then you can summarize it first before calculating cumulative.

    create a new calculated column with following DAX.

    Cumulative Earned =
    var _Sum = 
    SUMMARIZE(
        'Table',
        'Table'[Date],
        'Table'[Name],
        'Table'[WeeklyEarned]
    )
    Return
    SUMX(
        FILTER(
            _Sum,
            'Table'[Date]<=EARLIER('Table'[Date])&&
            'Table'[Name]=EARLIER('Table'[Name])
        ),
        'Table'[WeeklyEarned]
    )

     

    Hope this will help.

    Thank you.