Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Same day date difference resulting in negative value

Hello PBi Community   -   So I have the formula below which works great for calculating the number of weekdays between two dates.    I then use the result of this to tell me whether a shipment is on time, or late.    

 

One strange issue I have is that if the due date and the ship date fall on the exact same date, the result =  -1.      Ideally, it should just say 0.  

 

Any idea how to fix this?  

 

 

Due vs Ship Weekday Count =
If( 'Flu Shipped'[Due Date] < 'Flu Shipped'[Date Shipped],
CALCULATE(
COUNTROWS( DateTable ),
DATESBETWEEN ( DateTable[Date],  'Flu Shipped'[Due Date], 'Flu Shipped'[Date Shipped]),DateTable[DateIsWorkingDay]= True,
    ALL ( 'Flu Shipped' )
),

CALCULATE(
COUNTROWS( DateTable ),
DATESBETWEEN ( DateTable[Date], 'Flu Shipped'[Date Shipped], 'Flu Shipped'[Due Date]), DateTable[DateIsWorkingDay]=True,
    ALL ( 'Flu Shipped' )
) * -1)
Need Help
  • TomMartens's avatar
    TomMartens
    6 years ago

    Hey Anonymous ,

     

    as both dates are equal, the FALSE branch will be evaluated, and this means that the 1 row will be multiplied with -1

     

    Regards,

    Tom

3 Replies

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    6 years ago

    Hey Anonymous ,

     

    as both dates are equal, the FALSE branch will be evaluated, and this means that the 1 row will be multiplied with -1

     

    Regards,

    Tom

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago
      Thanks Tom. Is there an easy fix?
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks Tom....I realized I just needed to add an = sign to my first statement....then it made the days where the due date and ship date were the same to say  "1"   instead of  "-1".       Which technically is correct as the formula is counting the actual number of weekdays in the date range...which in those cases is "1"  (not Zero).      Thanks for pointing me in the right direction!