Forum Discussion
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...
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,
Hi Anonymous
You may have a look at DATEDIFF function.
Datediff = DATEDIFF(MAX(DateTable[Start date]),MAX(DateTable[End date]),MINUTE)
Regards,
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.
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,
This does not seem to be working - The Datediff column goes blank when I add the hour>24 column.
These are my results:
Hi Anonymous
Please create datediff measure with the above formula.
Regards,