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,
Based on the two tables that you have shared, please show the exact result you are expecting. Also, share data in a table format that can be easily pasted into an MS Excel file. Alternatively, store all data in an MS Excel file and share the download link of the Excel file.