Forum Discussion

Bharani's avatar
Bharani
Regular Visitor
7 years ago

Find time duration between 2 dates (Datetime format)

Hi Experts - Am new to Power BI.. Hope u can crack my query in a min...   I need to calculate "Number of minutes" between 2 dates which is in Datetime format..Below date is an example..   3/11/20...
v-piga-msft's avatar
v-piga-msft
Resident Rockstar
7 years ago

Hi Bharani,

 

You could try with the dax formula below to create the calculate column.

 

diff =
VAR tem = 'CountMinutes'[accepttime] - 'CountMinutes'[logtime]
RETURN
    HOUR ( tem ) * 60
        + MINUTE ( tem )

Then you will  get the output below.

 

 

Best Regards,

Cherry

  • Johanno's avatar
    Johanno
    Continued Contributor
    7 years ago

    Nice! However, that won't work when the logtime and accepttime are on different days, months or years?

    • v-piga-msft's avatar
      v-piga-msft
      Resident Rockstar
      7 years ago

      Hi Johanno Bharani,

       

      Try this formula below.

       

      Time = 
VAR DIFF = 'CountMinutes'[accepttime] - 'CountMinutes'[logtime]
VAR NumOfMinutes = DIFF * 24
    * 60
VAR DAYS =
    IF ( DIFF >= 1, INT ( DIFF ), BLANK () )
VAR HOURS =
    INT ( ( DIFF - DAYS ) * 24 )
VAR MINUTES = NumOfMinutes
    - ( DAYS * 24
    * 60 )
    - ( HOURS * 60 ) 
RETURN
    DAYS*24*60+HOURS*60+MINUTES

      Here is the output.

       

       

      Best  Regards,

      Cherry

  • Bharani's avatar
    Bharani
    Regular Visitor
    7 years ago

    Thanks!! Does it work if logtime and accepttime are on different days, months or years?

  • Bharani's avatar
    Bharani
    Regular Visitor
    7 years ago

    By the way, i get the error..

     

    • v-piga-msft's avatar
      v-piga-msft
      Resident Rockstar
      7 years ago

      Hi Bharani,

       

      Do you create the calculated column with the fomula?

       

      Could you show the detail error message? 

       

      Best Regards,

      Cherry

    • v-piga-msft's avatar
      v-piga-msft
      Resident Rockstar
      7 years ago

      Hi Bharani,

       

      Have you solved your problem?

       

      If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

       

      If you still need help, please feel free to ask.

       

      Best Regards,

      Cherry