Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
If I understand your question correctly, you're looking to adjust your DAX formula in such a way that if the `[MovedToApp]` timestamp is after the `[IN]` timestamp, the duration will show as a negative value.
Here's how you can make modifications to reflect this:
You're already calculating the difference in seconds using the `DATEDIFF` function. However, to handle the negative scenario, you can use an `IF` statement:
# of Secs APPTWAITtime =
IF(
'vw_BiReports_VirtualWaitTimes'[MovedToApp] > 'vw_BiReports_MeetingWaitTimes'[In],
-DATEDIFF('vw_BiReports_MeetingWaitTimes'[In],'vw_BiReports_VirtualWaitTimes'[MovedToApp],SECOND),
DATEDIFF('vw_BiReports_VirtualWaitTimes'[MovedToApp],'vw_BiReports_MeetingWaitTimes'[In],SECOND)
)
Here, if the `[MovedToApp]` timestamp is after the `[In]` timestamp, we make the duration negative.
Using the sign of `Duration`, decide the way you calculate hours, minutes, and seconds.
Chelsie Eiden's Duration AppWaittime =
VAR Duration = AVERAGE([# of Secs APPTWAITtime])
VAR AbsDuration = ABS(Duration) // Get the absolute value for calculations
VAR Hours = INT ( AbsDuration / 3600)
VAR Minutes = INT ( MOD( AbsDuration - ( Hours * 3600 ),3600 ) / 60)
VAR Seconds = ROUND( MOD ( MOD( AbsDuration - ( Hours * 3600 ),3600 ), 60 ),0)
// Decide the sign of the resulting time based on Duration's sign
RETURN
IF(Duration >= 0,
Hours * 10000 + Minutes * 100 + Seconds,
-(Hours * 10000 + Minutes * 100 + Seconds) // Negate the final result for negative durations
)
Thank you for your response!! Looks like I am getting close, what can I do return this just in mins? I tried this but doesn't look like its working. May be the format needs to be updated 00:00:00, can you please advise?
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
86 | |
84 | |
66 | |
62 | |
60 |
User | Count |
---|---|
197 | |
118 | |
108 | |
78 | |
69 |