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.
I have a calendar table that ranks business days pretty good as follows:
Rank = RANKX( FILTER( 'Calendar', Calendar[If work day] = 1 && Calendar[Month] = EARLIER( Calendar[Month] ) ), Calendar[Date],,ASC )
My issue comes when there is a weekend as the last day in a month. For example, April 29th and April 30th in 2017 were on the weekend. So the formula above tags 4/29 and 4/30 as business day 21 when there were only 20 business days in April 2017. How can I adjust this formula to have 4/29 and 4/30 be tagged as business day 20?
The only hesitation I have then is the reverse side. Again for April 2017, April 1st was a Saturday. So I would want both 4/1 and 4/2 to show as business day 1 which is actually 4/3 in April 2017.
The picture below is an example
Hi @Anonymous,
Sorry for the delay.
It should be feasible. Could you please share us your pbix file with One Drive or Google Drive if possible? So that I can make some proper tests.
Thanks,
Xi Jin.
@v-xjiin-msft I have uploaded an example file here: https://1drv.ms/u/s!AnhLSXIUf4QnadPIoMdTswn3tjk
Thanks
Hi @Anonymous,
Check this, hope it works for you:
Business Day = VAR RankID = RANKX( FILTER( 'Calendar', Calendar[If work day] = 1 && Calendar[Month] = EARLIER( Calendar[Month] ) ), Calendar[Date],,ASC ) Return IF('Calendar'[If work day]=1, RankID, RankID-1)
Thanks,
Xi Jin.
@v-xjiin-msft Sorry for the delay and thank you for the response. That works, however, I also need 4/2/2017 which you have circled in your screenshot to show up as 1. So if a month starts on a weekend, then those days should be recognized as business day 1. However, if a month ends on a weekend, those days should recognized as the last valid business day or the max business day for that particular month.
@v-xjiin-msft Nevermind, I think I answered my own question. Just added one more condition to your example:
Business Day2 = VAR RankID = RANKX( FILTER( 'Calendar', Calendar[If work day] = 1 && Calendar[Month] = EARLIER( Calendar[Month] ) ), Calendar[Date],,ASC ) RETURN VAR RankID2 = IF('Calendar'[If work day] = 1, RankID, RankID - 1) RETURN IF(RankID2 = 0 ,1, RankID2)
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |