Counting working days between two dates

[Anonymous]

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!

[Anonymous · Accepted solution]

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 · Accepted solution]

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 · Accepted solution]

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]

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!