Cumulative sum

[VJ_Lsf]

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])

))

[mdaatifraza5556]

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.

[Cookistador · Accepted solution]

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])

)

)

[rajendraongole1]

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.