Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
iDataDrew
Advocate IV
Advocate IV

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

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.

1 ACCEPTED SOLUTION
Phil_Seamark
Employee
Employee

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

 

weekdays.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

4 REPLIES 4
Phil_Seamark
Employee
Employee

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

 

weekdays.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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

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.

 

NewQCTable.PNG

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?

1.PNG

Best Regards,
Angelia

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.