Forum Discussion
Helper IIICumulative Hours based on Cumulative Days
I'm trying to figure out how to get a cumulative hours column based on the below dataset. The Count Consective Days column is based on the formula provided in this post - https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dax-Count-consecutive-days-and-sequence/td-p/1481277 which worked perfectly.
Count=
VAR vCurrentDate = Table1[Date]
VAR vCurrentID = Table1[ID]
VAR vPrevTbl =
FILTER ( Table1, Table1[Date] <= vCurrentDate && Table1[ID] = vCurrentID )
VAR vPrevDate =
MAXX (
FILTER (
vPrevTbl,
VAR vCD = Table1[Date]
VAR r =
MAXX ( FILTER ( vPrevTbl, Table1[Date] < vCD ), Table1[Date] )
RETURN
vcd - 1 <> r
),
Table1[Date]
)
RETURN
vCurrentDate - vPrevDate + 1
I now need to take it a step further and add a Cumulative Hours column based on consecutive days.
| EmployeeID | Charge Date | Count Consecutive Days | Hours | Cumulative Hours |
| 10050 | 3/1/2021 | 1 | 10 | 10 |
| 10050 | 3/2/2021 | 2 | 8 | 18 |
| 10050 | 3/3/2021 | 3 | 10 | 28 |
| 10050 | 3/4/2021 | 4 | 7 | 35 |
| 10050 | 3/5/2021 | 5 | 10 | 45 |
| 10050 | 3/6/2021 | 6 | 9 | 54 |
| 10050 | 3/8/2021 | 1 | 10 | 10 |
| 10050 | 3/9/2021 | 2 | 9 | 19 |
| 10050 | 3/10/2021 | 3 | 10 | 29 |
| 10050 | 3/11/2021 | 4 | 10 | 39 |
| 10170 | 3/1/2021 | 1 | 10 | 10 |
| 10170 | 3/2/2021 | 2 | 10 | 20 |
| 10170 | 3/3/2021 | 3 | 10 | 30 |
| 10170 | 3/4/2021 | 4 | 10 | 40 |
| 12365 | 3/1/2021 | 1 | 10 | 10 |
| 12365 | 3/2/2021 | 2 | 9 | 19 |
| 12365 | 3/3/2021 | 3 | 10 | 29 |
| 12365 | 3/4/2021 | 4 | 10 | 39 |
Any help would be greatly apprecated.
Hi mvgust ,
Here are the steps you can follow：
1. Click Transform data to enter the power query, select Add Column – index column – From 1
2. Create calculated column.
group = COUNTX(FILTER(ALL('Table (2)'),[Index]<=EARLIER([Index])&&[Count Consecutive Days]=1),[EmployeeID])Cumulative Hours = CALCULATE(SUM('Table (2)'[Hours]),FILTER('Table (2)',[Index]<=EARLIER([Index])&&[group]=EARLIER([group])))3. Result：
You can downloaded PBIX file from here.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi mvgust ,
Here are the steps you can follow：
1. Click Transform data to enter the power query, select Add Column – index column – From 1
2. Create calculated column.
group = COUNTX(FILTER(ALL('Table (2)'),[Index]<=EARLIER([Index])&&[Count Consecutive Days]=1),[EmployeeID])Cumulative Hours = CALCULATE(SUM('Table (2)'[Hours]),FILTER('Table (2)',[Index]<=EARLIER([Index])&&[group]=EARLIER([group])))3. Result：
You can downloaded PBIX file from here.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
mvgustThis works well thank you. I may run into issues in the future as I was getting the following message:
"there's not enough memory to complete this operation. please try again later when there may be more memory available"
Table currently has 1000 records but will continue to grow.