Error on Dax time conversion & Context

DAX DAX Calculations
1

HI,

Could some one please show me where Im going wrong with this ? basically i want to have in one table the Expected shift hours someone was supposed to work vs what they actually did i.e. clocked in through the gate at work.

I have both dax formulas working in their own tables however there is an error Im missing in the context somewhere i think as when i put both dax measures in one table i don’t see the result of one. Its the context of the selected value i think as one seems to run off the date the other runs off the shift type.

Thanks in advance

PBIX below

Capture
Capture1213×663 136 KB

Strata Staff Utilization.pbix (874.7 KB)

2

Hi @Krays23,

You have a duration in the Clockings table, so I used that had to convert though because it was text.

Actual Hours & Minutes Inside Strata = 
//CONVERT(SELECTEDVALUE('Gate Clockings'[Last Out (Joined)],0)-SELECTEDVALUE('Gate Clockings'[First In (Joined)],0),DATETIME)
CONVERT( 
    SUMX( 'Gate Clockings', 'Gate Clockings'[Duration] *1 ), 
    DATETIME
)

And for the time difference

Time Difference = 
VAR myVal = [Expected Hours & Minutes Inside Strata] - [Actual Hours & Minutes Inside Strata]
RETURN
IF( CONVERT( myVal, DOUBLE ) >0,
    FORMAT( myVal, "hh:mm" ) & " -",
    FORMAT( myVal, "hh:mm" )
)

Then I filtered your visual on [Actual Hours & Minutes Inside Strata] <> BLANK
with this result

image
image1183×538 43.7 KB

Here’s your sample file. Strata Staff Utilization.pbix (874.8 KB)
I hope this is helpful

1 Like
3

Your amazing thanks Melissa