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/2014  10:57:06 AM

 

i have 2 coulmns in my data as shown below.. Please help me in adding a new column in data which shows the difference between accept time & logtime from below screenshot.. it should show me in minutes..

 

For Eg, for the first one.. from below data it should show as 19 Mins

 

 

 

8 Replies

  • Johanno's avatar
    Johanno
    Continued Contributor
    7 years ago

    Hi, an ugly solution could be as a measure where I called my table CountMinutes:

    CountMinutes = SUMX(CountMinutes;
     YEAR(CountMinutes[accepttime])*1440*365+MONTH(CountMinutes[accepttime])*1440*30+DAY(CountMinutes[accepttime])*1440+HOUR(CountMinutes[accepttime])*60+MINUTE(CountMinutes[accepttime])
     -YEAR(CountMinutes[logtime])*1440*365-MONTH(CountMinutes[logtime])*1440*30-DAY(CountMinutes[logtime])*1440-HOUR(CountMinutes[logtime])*60-MINUTE(CountMinutes[logtime]))

    Someone will probably give us a more sophisticated solution. :smileyvery-happy:

  • 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?

      • 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