Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Can't select Date Field in Formulas

Good afternoon,    I am trying to caluclate the difference between a start date and an end date. I am trying to use the  Datediff formula, but I am unable to select the start or end date in my dat...
Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi Anonymous 

    You may create the datediff measure as below.Then add a measure to get the correct total.

    DateDiff_seconds = DATEDIFF(MAX(Table1[Start Date]),MAX(Table1[End Date]),SECOND)
    DateDiff_seconds_2 = IF(NOT(ISBLANK(SUM(Table1[Start Date]))),DATEDIFF(MAX(Table1[Start Date]),MAX(Table1[End Date]),SECOND))
    Total = SUMX(Table1,[DateDiff_seconds])

    Regards,

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Thank you. How would I get it to show the time difference in Hours? I change the final argument from Days to Hours, and that seems to work, but it rounds the hours, I'd like it to include minutes as well. 

v-cherch-msft's avatar
v-cherch-msft
Microsoft Employee
7 years ago

Hi Anonymous 

You may have a look at DATEDIFF function.

Datediff = DATEDIFF(MAX(DateTable[Start date]),MAX(DateTable[End date]),MINUTE)

Regards,

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thank you for your response. However, it does not quite solve my problem. I need the output of the formula to be in the "HH:MM" format. 

    • v-cherch-msft's avatar
      v-cherch-msft
      Microsoft Employee
      7 years ago

      Hi Anonymous 

      You may try below measures.As 24 hours could be supported in Time format in power bi.If the hours is over 24,you may use below measure to get it in text format.

      Datediff = DATEDIFF(MAX(DateTable[Start date]),MAX(DateTable[End date]),SECOND)
      hour>24 = var hours=TRUNC([Datediff]/3600) var minutes=TRUNC(MOD([Datediff],3600)/60) return hours&":"&minutes

      If hour<24,time format:

      Hour<24 = TIME(0,0,0)+[Datediff]

      Regards,

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        This does not seem to be working - The Datediff column goes blank when I add the hour>24 column.

         

        These are my results: