Cumulative Hours based in EmpID and ChargeDate

DAX DAX Calculations
1

I’m looking to add a calculated column to my below scenario. The basic premise, is I need to 1) Determine how many days in a row an employee worked and 2) Cumulative hours based on the cumulative days.

I found a formula for a calculated column for adding the cumulative days on the MS Power BI forum which is working exactly as I need but I’m struggling to accomplish the next step of cumulative hours.

image

I’ve attached a .pbix file with sample data along with the formula for the cumulative days count.

Thank you.

Example Data.pbix (47.3 KB)

2

Hi @mvgust

This is you looking for?

image
image1432×786 58.7 KB

I add two calculated column Cumulative Break to knows what is the high cumulative date and use to filter the final result on visual

Cumulative Break = 

VAR vCurrentDate = 'Test Data'[Charge Date]
VAR vCurrentID = 'Test Data'[Employee ID]
VAR vNextTbl =
    FILTER ( 'Test Data', 'Test Data'[Charge Date] = vCurrentDate + 1 && 'Test Data'[Employee ID] = vCurrentID )


RETURN
    IF(COUNTROWS(vNextTbl) >0 ,0,1)

and Cumulative Hour

Cumulative Hour = 

VAR vCurrentDate = 'Test Data'[Charge Date]
VAR _Ini = 'Test Data'[Cumulative Days Count] - 1
VAR _StartDate = DATEADD('Test Data'[Charge Date],-_Ini,DAY)
VAR vCurrentID = 'Test Data'[Employee ID]

VAR vNextTbl =
    FILTER ( 'Test Data', 'Test Data'[Charge Date] = vCurrentDate + 1 && 'Test Data'[Employee ID] = vCurrentID )
VAR vRangeTbl =
    FILTER ( 'Test Data', 'Test Data'[Charge Date] >= _StartDate && 'Test Data'[Charge Date]<= vCurrentDate  && 'Test Data'[Employee ID] = vCurrentID )

VAR _Acum = CALCULATE(SUM('Test Data'[Hours Worked]), ALL('Test Data'),vRangeTbl)
RETURN
    _Acum

Example Data-JoseBressan.pbix (49.3 KB)

3

Hi @mvgust, did the response provided by @jbressan help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

4

Hi @mvgust, we’ve noticed that no response has been received from you since the 30th of March. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

5

@jbressan

Yes, this is what I was looking for - thank you!!

1 Like