Forum Discussion

Mhscmp100's avatar
Mhscmp100
Regular Visitor
3 years ago
Solved

Need Help in Time Intelligence Functions

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.

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.