Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
@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
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |