Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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.
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.
Thank you Xi!! This worked and I learned so much reading this DAX. I appreciate your time. Yay!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
97 | |
80 | |
67 | |
63 |
User | Count |
---|---|
147 | |
110 | |
108 | |
85 | |
64 |