Forum Discussion
Helper IIIDATEDIFF working days but dates in different rows
I was wondering if anyone could help, v-jiascu-msft (ho is a star!, by the way) help me produced the following DAX:
Bascially it looks at the pervious row if the entityid is the same and calculates the numbers of hours between:
Calculated column Hours = VAR index = [Index] VAR previousTime = CALCULATE ( MIN ( 'msdyn_audithistories'[createdon] ), FILTER ( ALLEXCEPT ( msdyn_audithistories, msdyn_audithistories[msdyn_entityid] ), 'msdyn_audithistories'[Index] = index - 1 ) ) RETURN DATEDIFF ( previousTime, 'msdyn_audithistories'[createdon], HOUR )
However this is obviously 24 hours time period. Does anyone have any suggestions on limiting this to working hours.
I have done this previously through a date table and clacualting the start time and the end time and the hours between these, but suggling seeing the start time is in a different row (previous row) but only if the entitiy ID is the same
Any help would be great
Many thanks
Tim
tmears,
You can create a previoustime column using DAX below in your table, then calculate the working hours between createdon column and previoustime column following the guide in this video. You would need to create extra tables such as date table and holiday table.previoustime = CALCULATE(FIRSTNONBLANK('msdyn_audithistories'[createdon],1),FILTER(msdyn_audithistories, msdyn_audithistories[msdyn_entityid]=EARLIER( msdyn_audithistories[msdyn_entityid]) && msdyn_audithistories[Index]=EARLIER(msdyn_audithistories[Index])-1))
Regards,
Lydia
1 Reply
tmears,
You can create a previoustime column using DAX below in your table, then calculate the working hours between createdon column and previoustime column following the guide in this video. You would need to create extra tables such as date table and holiday table.previoustime = CALCULATE(FIRSTNONBLANK('msdyn_audithistories'[createdon],1),FILTER(msdyn_audithistories, msdyn_audithistories[msdyn_entityid]=EARLIER( msdyn_audithistories[msdyn_entityid]) && msdyn_audithistories[Index]=EARLIER(msdyn_audithistories[Index])-1))
Regards,
Lydia