Days between Dates with TODAY and End Date before Start Date

[robhel]

I am really after some much needed help here after reading through and experimenting with a number of prior posts, however none of them are assisting me.

I am after a column that will calculate the days between two date fields, with TODAY() used in any blank end date fields, with one other parameter is that some scheduled start dates are after the actual completion date

I’ve tried both DATEDIFF (however got error message of start date cannot be before end date) and also DATESBETWEEN (however got error message of only able to use dates in this function)

Days Design = IF(ISBLANK([DESIGN_ACT]),DATEDIFF([DESIGN_SCHED],TODAY(),DAY),DATEDIFF([DESIGN_SCHED],[DESIGN_ACT],DAY))

Any assistance in DAX code to complete this would be greatly appreciated.

Thanking you

[robhel]

Thank you for your fast responses to my question & yes, downloading the latest BI desktop performed the DATEDIFF function

[Greg_Deckler]

I thought that they had fixed that with DATEDIFF, what version of Power BI are you on?

You can always use:

Column 2 = ([Column2].[Date]-[Column1].[Date]) * 1.

Make sure that the new column is set to type Whole number. This will give you the days between the dates and will return negatives.

[Greg_Deckler · Accepted solution]

I thought so, the DATEDIFF function was fixed to return negative numbers in like the June version of Power BI Desktop. Perhaps earlier. Just tried it and it worked perfectly.

[OwenAuger]

@robhel

You can simply subtract one date from another (and multiply the result by 1 to force an integer to be returned) to get the difference in days. Subtracting dates will never return an error, but you will get a positive or negative result depending on the order of the dates.

I would recommend DAX similar to below, including the table name before any column references to avoid confusion with measures:

Days Design =
VAR EndDate =
    IF ( ISBLANK ( TableName[DESIGN_ACT] ), TODAY (), TableName[DESIGN_ACT] )
RETURN
    1 * ( EndDate - TableName[DESIGN_SCHED] )

DATEDIFF performs the same function if the third argument is DAY, but in some versions of the engine has the limitation you encountered where the first date must not be later than the second date.

DATESBETWEEN returns a single-column table of dates taken from a date column in a physical table, between specified start and end dates. This function is normally used to produce a set of dates to be used as a filter, such as within the CALCULATE function.

Regards

Owen