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.
Can anyone help me with the issue?
You need to add a "reset group" then add it in formula
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.