Sum Business Days between two dates with Issues

[ihartdata]

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?

[v-xjiin-msft · Accepted solution]

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

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))))

Thanks,
Xi Jin.

[ihartdata]

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