Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have an issue: Approach of calculation network days does not work. I Prepared everything by instruction which is presented here (https://www.youtube.com/watch?v=9M1V_m-oEzc)
Link on pbix.
https://app.box.com/s/8jqxm88ik0850lh2llt6zrfwyyek056r
Solved! Go to Solution.
Hi @Anonymous,
We can take the following steps to meet your requirement.
1. Update one of your formulas
From
WorkingDays = IF(OR('Date'[Date]=6, 'Date'[Date]=7),0, 1)
To
WorkingDays = IF(OR('Date'[WeekDay]=6,'Date'[WeekDay]=7),0,1)
2. Create a new column.
workday = IF('Date'[WorkingDays]=1,DAY('Date'[Date]),BLANK())
3. Then we can get the result we need.
For more details, please check the pbix as attached.
https://www.dropbox.com/s/3b68yfqbdz3vo9n/NetWorkDays2.1.pbix?dl=0
Regards,
Frank
Hi @Anonymous,
Based on my test, I can calculate the net workdays by the way as the video.
1. Create a Date table and a holiday table.
2. Add two columns in the Date one.
weekday = WEEKDAY('date'[Date])
weekdays = IF(OR('date'[weekday]=6,'date'[weekday]=7),0,IF(ISBLANK(RELATED(Hiliday[holi])),1,0))
3. To get the new workdays, we can use this measure as below.
days = CALCULATE(SUM('date'[weekdays]),FILTER('date','date'[weekdays]=1))
Then we can get the result as below.
For more details, please check the pbix as attached.
By the way, I cannot get your pbix you shared. Could you please share that to me again if necessary?
Regards,
Frank
Hi Frank!
Could you please check this link: https://app.box.com/s/8jqxm88ik0850lh2llt6zrfwyyek056r
I see that it works in case when you have additional data by holiday.
But I need to exclude just weekends (Saturday and Sunday)
As a result I would like to see something like that:
Hi @Anonymous,
We can take the following steps to meet your requirement.
1. Update one of your formulas
From
WorkingDays = IF(OR('Date'[Date]=6, 'Date'[Date]=7),0, 1)
To
WorkingDays = IF(OR('Date'[WeekDay]=6,'Date'[WeekDay]=7),0,1)
2. Create a new column.
workday = IF('Date'[WorkingDays]=1,DAY('Date'[Date]),BLANK())
3. Then we can get the result we need.
For more details, please check the pbix as attached.
https://www.dropbox.com/s/3b68yfqbdz3vo9n/NetWorkDays2.1.pbix?dl=0
Regards,
Frank
Hi @Anonymous,
Does that make sense? If so , could you please mark this answer as a solution?
Regards,
Frank
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |