topic Re: Need Help in Time Intelligence Functions in DAX Commands and Tips

Need Help in Time Intelligence Functions

Mhscmp100 — Tue, 18 Jul 2023 07:50:43 GMT

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.

Re: Need Help in Time Intelligence Functions

Anonymous — Thu, 20 Jul 2023 03:11:08 GMT

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.