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 ...
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!
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!