Forum Discussion
DAX, Disaggregate PLAN data - Excluding weekends and Public holidays
- 5 years ago
Hi bbqq021 ,
You could try the following steps:
Step1,use the following dax ,to create data table:
Dates = CALENDAR("2016/1/1","2017/12/31")Step2, import holiday in 2016 and 2017,refer:
Creating A Calendar Table For Holidays In Power BI
https://blog.enterprisedna.co/creating-a-calendar-table-for-holidays-in-power-bi/
And import web link :
https://www.calendarpedia.com/holidays/federal-holidays-2016.html
https://www.calendarpedia.com/holidays/federal-holidays-2017.html
And you will see the below:
Step 3, base on dates,and holiday table ,to create a new table ,use the following dax(when weekend and holiday ,the column return false)
date1 = GENERATE ( CALENDARAUTO (), VAR yyyy = YEAR ( [Date] ) VAR mmmm = MONTH ( [Date] ) RETURN ROW ("Date1",FORMAT ( [Date], "YYYY-MM-DD" ), "Year", yyyy, "Month", FORMAT ( [Date], "mmmm" ), "Month2", mmmm, "Week1", FORMAT ( [Date], "dddd" ), "Week2", WEEKDAY ( [Date] ), "weekday", NOT WEEKDAY ( [Date] ) IN { 1, 7 } && not [Date] in VALUES('2016holiday'[Date])&& not [Date] in VALUES('2017holiday'[Date]) ) )Step 4,use the dax to get the day except weekend and holiday,and then get average:
difforder1 = VAR day1 = CALCULATE ( COUNTROWS ( 'date1' ), DATESBETWEEN ( date1[Date], Original[Start Date], Original[End Date]), date1[weekday] <> TRUE, ALL ( Original ) ) VAR day2 = IF ( day1 = BLANK (), 0, day1 ) VAR day3 = DATEDIFF ( Original[Start Date], Original[End Date], DAY ) - day2 RETURN day3AVERAGE = Original[Resource]/Original[difforder1]Final you will get the below:
Wish it is helpful for you!
Best Regards
Lucien
Hi bbqq021
Am not sure I understood your point however in the file attached I added 3 columns where you be able to filter out the holidays and weekeds from your visual.
Just for this formula
https://drive.google.com/file/d/1VsxGNFc-_ayIBurnBHB4hNJ4b_4827OM/view?usp=sharing
Let me know
Hi aj1973
thanks for your idea however its not the full solution. The first problem is to disaggregate the data which is spread over multiple days down to a single day so i have the resource for every single day between the start and end. The second part is to exclude the weekends and public holidays.
The original solution used manipluation in power query which worked well however i am not sure how to apply this to just working days and not every day.
If you have look at my Pbix file you can see the steps i have take to go from 4 rows of data to many.