Number of days between two dates, but only counting work/business days...

[iDataDrew]

Sample data: https://www.dropbox.com/sh/lzgqonrufze6mql/AABDGjIHOvu7iraqXeqKTmA9a?dl=0

We have a "PromiseDate" which is when we tell the customer it will be ready. The product needs to be passed quality control (DatePassedQC) at least one work day prior to the PromiseDate. If the DatePassedQC is on or after the PromiseDate, then the order is late by the number of workdays between the two dates.

So in short, I need to calculate the number of workdays between the PromiseDate and the DatePassedQC date, but also keep in mind that sometimes the DatePassedQC date will be before the PromiseDate and other times it will be after the PromiseDate.  You should be able to access the sample files, which represent the date tables I'm working with.

Thank you for your help.

[Phil_Seamark · Accepted solution]

Hi @iDataDrew

I created the following Calculated Table using your data. If it's close, it may only need a slight tweek around the < and > operators on the FILTER

New QCTable = SUMMARIZE(
            FILTER(
			 CROSSJOIN(QCTable,'DateTable'),
             [Date] >= [PromiseDate] 
             && [Date] <= [DatePassQC] 
             && [IsWorkDay] = true()
             ),
             [PromiseDate],
             QCTable[DatePassQC],
             "Count of Week Days",
             COUNTA('DateTable'[Date])
             )

Which gave me this

[iDataDrew]

@v-huizhn-msft and @Phil_Seamark I figured it out.  @Phil_Seamark's solution ended up working.  For some reason, it didn't work when I had a relationship between the QC table and the DimDate table.  When I deleted that relationship, then the New QCTable worked.  Any ideas on why that would be?  Either way, thank you for the solution!

[iDataDrew]

Thank you for the quick response @Phil_Seamark.  See screenshot below.  I used the same calculated table, but did not get the same result.  Have any ideas on why that might be?  Thank you.

[v-huizhn-msft]

Hi @iDataDrew,

After tested, you it return the same result like @Phil_Seamark. Please confirm you have created a new table(by click "New Table" under Modeling on home page) rether than a calculated column?



Best Regards,
Angelia