Need Help in Time Intelligence Functions

[Mhscmp100]

Hi Guys,

Does anyone know how to calculate time variance exactly as we calculate it in Excel? excel provide an option of the 1904 data system so power bi also provides the same or not?  

For example;

I have 2 times available in 2 different columns with names as;

actual departure time 21:00

actual arrival time 00:36

time variance = actual arrival time  - actual departure time 

time variance = 00:36 - 21:00

power bi shows the output as 20:24 instead of 03:36 which is shown in Excel by formatting the 1904 data system.

[Anonymous · Accepted solution]

Hi @Mhscmp100 ,

Please try below steps:

1. below is my test table

Table:

2. create a new column with below dax formula

Time Variance =
VAR val =
    IF ( [arrival time] < [departure time], [arrival time] + 1, [arrival time] ) - [departure time]
RETURN
    FORMAT ( val, "hh:nn" )

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.