Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Counting working days between two dates

Hi, I'm using the following formula to calculate working dates between two dates. It works great if the PickedDate comes after Delivery date. However, the formula returns a 0 instead of the negative value, which is expected, when the PickedDate comes before the DeliveryDate. As the result, the DeliveryDaysVariance is always positive. Please help me create a formula that will generate either positive or negative variance depending whether the PickedDate comes before or after the DeliveryDate. I need to count only working days, so I created a the IsWorkDay column in my RollingDate table that mark working days as 1.

 

DeliveryDaysVariance = CALCULATE(SUM(RollingDate[IsWorkDay]),
                                    DATESBETWEEN(RollingDate[Date],
                                    Invoices[DeliveryDate],
                                    Invoices[PickedDate])) + 0

Thank you!

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

     

    Hi,

     

    Try if the following codes work for you...

     

    DeliveryDaysVariance =
CALCULATE (
    SUM ( RollingDate[IsWorkDay] ),
    DATESBETWEEN ( RollingDate[Date], MIN(Invoices[DeliveryDate], Invoices[PickedDate]),MAX(Invoices[DeliveryDate], Invoices[PickedDate]) )
)
    + 0 
  • Anonymous's avatar
    Anonymous
    7 years ago
    I made your formula work by adding a conditional column in the query edit. This column has -1 when the PickDate is prior to the DeliveryDate and 1 when it's after. Thank you!

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

     

    Hi,

     

    Try if the following codes work for you...

     

    DeliveryDaysVariance =
CALCULATE (
    SUM ( RollingDate[IsWorkDay] ),
    DATESBETWEEN ( RollingDate[Date], MIN(Invoices[DeliveryDate], Invoices[PickedDate]),MAX(Invoices[DeliveryDate], Invoices[PickedDate]) )
)
    + 0 
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago
      I made your formula work by adding a conditional column in the query edit. This column has -1 when the PickDate is prior to the DeliveryDate and 1 when it's after. Thank you!
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks for your response. That's an iteresting solution but it doens't indicate negative variances when the PickDate is prior to DeliveryDate. Your code returns only positive variances. I just wrote the following code which seems to do the job. I was hoping there was a more elegant way of achieving the same result:

       

      DeliveryDaysVariance = IF(Invoices[PickedDate]>Invoices[DeliveryDate]  
                                    ,CALCULATE(SUM(RollingDate[IsWorkDay]),
                                                    DATESBETWEEN(RollingDate[Date],
                                                    Invoices[DeliveryDate],
                                                    Invoices[PickedDate])) + 0
                                    ,-1*CALCULATE(SUM(RollingDate[IsWorkDay]),
                                                    DATESBETWEEN(RollingDate[Date],
                                                    Invoices[PickedDate],
                                                    Invoices[DeliveryDate])) + 0)

      Thank you!