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 dataset within the formula bar. The two fields are both in the "Date" format, and I can select them when using aggregate functions, such as "Sum" or "Max", but not in the Datediff formula. What is the reason for this?

 

Thank you for your assistance. 

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,

13 Replies

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