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 ,
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
day3
AVERAGE = Original[Resource]/Original[difforder1]
Final you will get the below:
Wish it is helpful for you!
Best Regards
Lucien