Forum Discussion

VJ_Lsf's avatar
VJ_Lsf
Frequent Visitor
1 year ago
Solved

Cumulative sum

Hi,

I need help with creating a measure for calculating cumulative sum by technician. I also have a date column in addition to 2 columns below. Cumulative total by Technician column is what I need. I have the measure below that calculates cumulative sum by dates but doesn't give the cumulative sum by the technician. How can I add Technician window to this. Thanks in advance for your help.

CALCULATE(
SUM(table[value]),
FILTER(ALLSELECTED('table'),
'Table'[date]<=MAX('table'[date])
))

 

Need Help
  • Cookistador's avatar
    Cookistador
    1 year ago

    Hi VJ_Lsf 

    Hello,

    I made a small test:

     

    As mentionned, with your query I get the third line 

    The sum is done by month (9 then 18 and 33)

     

    To get the sum by Date AND Techncian, you have to add the technician in the column

    you just have to add:

    'Table'[Technician] = MAX('Table'[Technician]) in your dax query, so your new measure becomes
     
    Cumulative Total by Date & Technician =
    CALCULATE(
    SUM('Table'[Value]),
    FILTER(
    ALLSELECTED('Table'),
    'Table'[Date] <= MAX('Table'[Date]) &&
    'Table'[Technician] = MAX('Table'[Technician])
    )
    )

     

     

     

3 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    Hi VJ_Lsf  - You already have a cumulative sum measure that works by date, but now you want to calculate the cumulative sum by Technician and Date

     

    Cumulative Total by Technician =
    CALCULATE(
    SUM('Table'[Value]),
    FILTER(
    ALLSELECTED('Table'),
    'Table'[Technician] = MAX('Table'[Technician]) &&
    'Table'[Date] <= MAX('Table'[Date])
    )
    )

     

    check the above modified measure . Hope it works.

     

     

  • Cookistador's avatar
    Cookistador
    Super User
    1 year ago

    Hi VJ_Lsf 

    Hello,

    I made a small test:

     

    As mentionned, with your query I get the third line 

    The sum is done by month (9 then 18 and 33)

     

    To get the sum by Date AND Techncian, you have to add the technician in the column

    you just have to add:

    'Table'[Technician] = MAX('Table'[Technician]) in your dax query, so your new measure becomes
     
    Cumulative Total by Date & Technician =
    CALCULATE(
    SUM('Table'[Value]),
    FILTER(
    ALLSELECTED('Table'),
    'Table'[Date] <= MAX('Table'[Date]) &&
    'Table'[Technician] = MAX('Table'[Technician])
    )
    )

     

     

     

  • mdaatifraza5556's avatar
    mdaatifraza5556
    Super User
    1 year ago

    Hi VJ_Lsf 

    Can you please try the below DAX?

    Cumulative Sum by Technician =
                                         CALCULATE (
                                                     SUM ( 'Table'[value] ),
                                                     FILTER (
                                                         ALLEXCEPT ( 'Table', 'Table'[Technician] ),
                                                        'Table'[date] <= MAX ( 'Table'[date] )
                                                     )
                                          )


    If this answers your questions, kindly accept it as a solution and give kudos.