DATEDIFF early/late excluding weekends/holidays

[Anonymous]

I currently have a column "DAYS VARIANCE" for the difference between two dates, calculated as "DATEDIFF(PUR_ONTIME[REQ DATE].[Date],PUR_ONTIME[REC DATE],DAY)". This calculation can return negative values (early deliveries) or positive values (late deliveries). I need this calculation to exclude weekends and holidays. I already have a column with a "1" for work days and a "0" for weekends/holidays. I can't simply add or subtract non-workdays since there could be positive or negative values. I suppose I could use a clunky "IF" function... but is there an easier way to do this?

I'm brand new to PBI.

[v-xuding-msft]

Hi @Anonymous ,

I create a sample you can reference to modify your formulas.

Total Working Days =
SUMX (
    FILTER (
     Table2,
        'Table2'[Date] >= MIN(PUR_ONTIME[REQ DATE])
            && 'Table2'[Date] <= MAX(PUR_ONTIME[REC DATE])
    ),
    Table2[is work days]
)

Best Regards,

Xue Ding 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-xuding-msft,

Thank you so much for your response! Unfortunately, your measure only returns a positive sum of working days for late deliveries where [REC DATE] >= [REQ DATE]. I need it to also return a negative sum of working days for early  deliveries where [REC DATE] < [REQ DATE]. That is why I said "I can't simply add or subtract non-workdays since there could be positive or negative values," in other words, a simple sum function doesn't work. The measure should also return a '0' if [REC DATE] = [REQ DATE], regardless of whether or not it is a work day.

Alex