Forum Discussion

jsquaredz's avatar
jsquaredz
Icon for Advocate I rankAdvocate I
9 years ago
Solved

Datediff start date cannot be greater than the end date

I have been using DAX DATEDIFF statement  to calculate No Days between two dates which are Target Completion Date and Actual Completion Date   Which works all very well except now I have sometimes...
Need Help
  • Framet's avatar
    Framet
    9 years ago

    Hi,

     

    Whilst I can't answer the "why" it can't return a negative integer for dates where the start date is after the end date I can suggest you could simply handle it with a IF statement. Test your dates if the start date is greater than the end date then change the sign of the result datediff with the dates passed to the function the other way round.

    DateDiffMeasure :=
IF (
    [StartDateField] > [EndDateField],
    0 - DATEDIFF ( [EndDateField], [StartDateField], DAY ),
    DATEDIFF ( [StartDateField], [EndDateField], DAY )
)

    Might work for you?

heidibb's avatar
heidibb
Icon for Helper IV rankHelper IV
9 years ago

I realize this is an older topic, but I came across it when searching for a similar solution. I wanted to share that this solution also worked for me:

 

Days Btwn = -1 * (format(start_date, "MM-DD-YYYY") - format(end_date, "MM-DD-YYYY")) 

 

I needed to add the format since our dates have timestamp and I wanted the difference in days regardless of the hour. If you are ok with getting portions of days, leave off the format!

 

Hope this  helps someone!