cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
viialar Visitor
Visitor

Cumulative sum that resets

I have data with columns date, person ID, starting time and durance columns. In this model the starting time is 6, 7, 8, 9, 10 or 11 and the durance is always 1 (an hour). Each person ID is participating an event 1-6 hours per day or a few 6-hour-days in a row. If the person ID is not participating there are no rows, no null values.

If one is participating today starting at 11 and again tomorrow at 6 it's considered to be consecutive. A special case that person ID is participating today the whole day and tomorrow starting from 10 (not 6) is not consecutive.

 

For each person ID, I need to calculate the cumulative sum of the consecutive durance values so that it resets when the person ID is not participating. I manage to get the daily culumative sum but can't get any further.
I don't know whether is easier to use DAX or edit query and creating new tables. I'm just learning Power BI so the skills are not yet super. Smiley Happy

 

Can anyone help me with the issue? 

1 REPLY 1
Community Support Team
Community Support Team

Re: Cumulative sum that resets

hi, @viialar 

You need to add a "reset group"  then add it in formula

For example:

For a cumulative sum by the date that reset by year.

    FILTER (
        ALL ( 'Date'[Date] ),
        AND (
            'Date'[Date] <= MAX ( 'Date'[Date] ),
            YEAR ( 'Date'[Date] ) = YEAR ( MAX ( 'Date'[Date] ) )
        )
    )

If not your case, please share your sample data and expected output.

 

Best Regards,
Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.