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

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.