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,

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

Hi Anonymous 

You may use aggregate function in the measure formula like below:

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

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.