Forum Discussion

JenWilson's avatar
JenWilson
Icon for Helper II rankHelper II
2 years ago
Solved

Days 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 DateProduc...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    2 years ago

    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

     

ryan_mayu's avatar
ryan_mayu
Icon for Super User rankSuper User
2 years ago

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

 

JenWilson's avatar
JenWilson
Icon for Helper II rankHelper II
2 years ago

Your 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]))-1
VAR _DateDiffLate = CALCULATE(SUM(Calendar[WeekDays]),DATESBETWEEN(Calendar[Date],FactTable[DateProduced],FactTable[DateShipped]))-1
RETURN IF(_DateDiffEarly>=0,_DateDiffEarly,_DateDiffLate*-1)