Forum Discussion
Helper IIDays between dates not showing negatives
The calculation below is giving the correct number of days between my fact table columns (DateProduced and DateShipped) but only if the DateProduced is earlier than the DateShipped. If the DateProduced is any date after DateShipped, I get a -1 (due to the last part of the formula below, else it would be blank). So if the DateProduced is 8/26/24 and the DateShipped is 8/27/24 or 8/28/2024 it will always show -1. What I need it to show is the true negative value for the dates between those dates. Note the calendar table is already identifying weekdays as the first part of my formula below shows.
What can I add to this formula to correctly display the negative days between?
DaysEarly/Late = CALCULATE(SUM(Calendar[WeekDays]),DATESBETWEEN(Calendar[Date],FactTable[DateProduced],FactTable[DateShipped]))-1
maybe you can try this
if (FactTable[DateProduced]<=FactTable[DateShipped], CALCULATE(SUM(Calendar[WeekDays]),DATESBETWEEN(Calendar[Date],FactTable[DateProduced],FactTable[DateShipped])), CALCULATE(SUM(Calendar[WeekDays]),DATESBETWEEN(Calendar[Date],FactTable[DateShipped],FactTable[DateProduced]))*-1)
if the produced date is earlier than ship date, then use the current foumula, else change the position of these two dates in the existing formular and * -1 to get a negative day number
2 Replies
ryan_mayuSuper User
maybe you can try this
if (FactTable[DateProduced]<=FactTable[DateShipped], CALCULATE(SUM(Calendar[WeekDays]),DATESBETWEEN(Calendar[Date],FactTable[DateProduced],FactTable[DateShipped])), CALCULATE(SUM(Calendar[WeekDays]),DATESBETWEEN(Calendar[Date],FactTable[DateShipped],FactTable[DateProduced]))*-1)
if the produced date is earlier than ship date, then use the current foumula, else change the position of these two dates in the existing formular and * -1 to get a negative day number
JenWilsonYour solution didn't quite work. I did something similar as below and I am getting the desired results.
Prod to Req Ship Days =VAR _DateDiffEarly = CALCULATE(SUM(Calendar[WeekDays]),DATESBETWEEN(Calendar[Date],FactTable[DateShipped],FactTable[DateProduced]))-1VAR _DateDiffLate = CALCULATE(SUM(Calendar[WeekDays]),DATESBETWEEN(Calendar[Date],FactTable[DateProduced],FactTable[DateShipped]))-1RETURN IF(_DateDiffEarly>=0,_DateDiffEarly,_DateDiffLate*-1)