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 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:

 

 

 

 

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

    Hi Anonymous 

    Please create datediff measure with the above formula.

    Regards,

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      This worked, thank you. However, I need to be able to sum the the total for each entry, but since we used Max in the DateDiff formula, the total is coming out as the highest value in the data. So in your example, the sum would be "4:17" when in fact I need it to be "28:17", the sum of the difference. I changed our DateDiff formula from Max to Sum, but this throws the error "An invalid numeric representation of a date value was encountered". I've been researching, and my guess is that it's being caused by some blanks in the data. How would I get around that error? 

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        7 years ago

        Hi,

        Share the link from where i can download your PBI file.