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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ihartdata
Microsoft Employee
Microsoft Employee

Sum Business Days between two dates with Issues

Hi,

 

I found this community super helpful with my last post where I was trying to count the days between two dates, then I realized what I really needed was to count the business days between two dates.

 

I need to count the number of business days between Table 'Contacts'[Request In DATE] and 'Contacts'[Invite Out DATE].  I have a related Table 'Calendar Table' that has column [Date] and [Is Business Day] that represents business days as 1 and non-business days as 0.  The only issue is that in Table 'Contacts' there are Blanks in the [Invite Out DATE] and [Request In DATE] as well as some scenarios where the [Request In DATE] is AFTER the [Invite Out DATE].

 

In order to count the days between these dates I used the following DAX and it works great! Now I just need to add in some type of filter to ONLY count the days that are Business Days:

 

Days to Enroll = IF(OR(ISBLANK('Contacts'[Request In Date]),ISBLANK('Contacts'[Invite Out Date])),3,IF(OR('Contacts'[Invite Out Date]<'Contacts'[Request In Date],'Contacts'[Request In Date]>'Contacts'[Invite Out Date]),-1*DATEDIFF('Contacts'[Invite Out Date],'Contacts'[Request In Date],DAY),DATEDIFF('Contacts'[Request In Date],'Contacts'[Invite Out Date],DAY)))

 

Below is a quick image of sample data. How can I do this?

business days to invite.PNG

1 ACCEPTED SOLUTION
v-xjiin-msft
Solution Sage
Solution Sage

@ihartdata

 

Since I don’t have the entire calendar which contains IsBusinessDay flag. I have created a short part of this calendar as from 07/03/2014 to 07/20/2014.

 

Then to achieve your requirement, first you should create a relation between Table 'Contacts' and Calendar table.

 

111.PNG

 

Then try following DAX:

 

Column = IF(OR(ISBLANK('Contacts'[Request In Date]),ISBLANK('Contacts'[Invite Out Date])),
            3,
            IF(OR
                ('Contacts'[Invite Out Date] < 'Contacts'[Request In Date],'Contacts'[Request In Date] > 'Contacts'[Invite Out Date]),
            -1 *CALCULATE(COUNT('Calendar'[IsBusinessDay]),FILTER('Calendar','Calendar'[Date] < Contacts[Request In DATE] && 'Calendar'[Date]>=Contacts[Invite Out DATE] && 'Calendar'[IsBusinessDay] = 1)),
            CALCULATE(COUNT('Calendar'[IsBusinessDay]),FILTER('Calendar','Calendar'[Date] >= Contacts[Request In DATE] && 'Calendar'[Date]<Contacts[Invite Out DATE] && 'Calendar'[IsBusinessDay] = 1))))

 

 

222.PNG

 

Thanks,
Xi Jin.

View solution in original post

2 REPLIES 2
v-xjiin-msft
Solution Sage
Solution Sage

@ihartdata

 

Since I don’t have the entire calendar which contains IsBusinessDay flag. I have created a short part of this calendar as from 07/03/2014 to 07/20/2014.

 

Then to achieve your requirement, first you should create a relation between Table 'Contacts' and Calendar table.

 

111.PNG

 

Then try following DAX:

 

Column = IF(OR(ISBLANK('Contacts'[Request In Date]),ISBLANK('Contacts'[Invite Out Date])),
            3,
            IF(OR
                ('Contacts'[Invite Out Date] < 'Contacts'[Request In Date],'Contacts'[Request In Date] > 'Contacts'[Invite Out Date]),
            -1 *CALCULATE(COUNT('Calendar'[IsBusinessDay]),FILTER('Calendar','Calendar'[Date] < Contacts[Request In DATE] && 'Calendar'[Date]>=Contacts[Invite Out DATE] && 'Calendar'[IsBusinessDay] = 1)),
            CALCULATE(COUNT('Calendar'[IsBusinessDay]),FILTER('Calendar','Calendar'[Date] >= Contacts[Request In DATE] && 'Calendar'[Date]<Contacts[Invite Out DATE] && 'Calendar'[IsBusinessDay] = 1))))

 

 

222.PNG

 

Thanks,
Xi Jin.

Thank you Xi!!  This worked and I learned so much reading this DAX.  I appreciate your time. Yay!

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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