Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mvgust
Helper III
Helper III

Cumulative 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/...  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. 

EmployeeIDCharge DateCount Consecutive DaysHoursCumulative Hours
100503/1/202111010
100503/2/20212818
100503/3/202131028
100503/4/20214735
100503/5/202151045
100503/6/20216954
100503/8/202111010
100503/9/20212919
100503/10/202131029
100503/11/202141039
101703/1/202111010
101703/2/202121020
101703/3/202131030
101703/4/202141040
123653/1/202111010
123653/2/20212919
123653/3/202131029
123653/4/202141039


Any help would be greatly apprecated.

 

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

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

v-yangliu-msft_0-1616984068001.png

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:

v-yangliu-msft_1-1616984068007.png

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.

View solution in original post

2 REPLIES 2
v-yangliu-msft
Community Support
Community Support

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

v-yangliu-msft_0-1616984068001.png

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:

v-yangliu-msft_1-1616984068007.png

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.

This 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. 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.