Forum Discussion

ihartdata's avatar
ihartdata
Microsoft Employee
8 years ago
Solved

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 ...
Need Help
  • v-xjiin-msft's avatar
    v-xjiin-msft
    8 years ago

    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.

v-xjiin-msft's avatar
v-xjiin-msft
Solution Sage
8 years ago

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's avatar
ihartdata
Microsoft Employee
8 years ago

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