Need to show the negative duration

[sraj]

Hi there,

 Is there anyway I can show the negative value if the individual is Moved to the queue [MovedToApp] after the Appt date & time [IN]?

Appt date & time [IN] = 6/07/2023 9:45:00 AM - Moved to the queue [MovedToApp] = 6/07/2023  1:01:21 PM = 03:16:21

I am calculating as mentioned below :

# of Secs APPTWAITtime = (DATEDIFF('vw_BiReports_VirtualWaitTimes'[MovedToApp],'vw_BiReports_MeetingWaitTimes'[In],SECOND))

Chelsie Eiden's Duration AppWaittime =

// We start with a duration in number of seconds

VAR Duration = AVERAGE([# of Secs APPTWAITtime])

// There are 3,600 seconds in an hour

VAR Hours = INT ( Duration / 3600)

// There are 60 seconds in a minute

VAR Minutes = INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60)

// Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours

VAR Seconds = ROUND( MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0) // We round up here to get a whole number

RETURN

// We put the hours, minutes and seconds into the proper "place"

Hours * 10000 + Minutes * 100 + Seconds

Can someone please advise?

[AmiraBedh]

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
)

[sraj]

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?

RETURN

IF(Duration >= 0,

Minutes * 100,

-(Minutes * 100) 

)