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
Many thanks aj1973 however thats not exactly what i was looking for.
Let me provide some more detail. This is my original data:
There are 4 lines and the resource is to be spent between the start and end date however because there are only 4 lines it has to be reported on the start OR end.
I want to disaggregate this data so it is spread evenly across the days between start and end date. This is the solution Vvelarde provided however i now want to exclude weekends and public holidays. The dissaggregation should look like this:
The solution was to make this calculation in power query however i am not sure how to identify weekends and public holidays and exclude them here.
I have saved the pbix with the initial solution and my data here.
https://www.dropbox.com/s/7us6cs10482tsk2/Disaggregate%20Plan%20Data.pbix?dl=0
Many thanks
Will
- aj19735 years ago
Community Champion
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
Public Holidays = IF(Transformed[Date] IN {DATE(2016,01,01), DATE(2017,01,01), DATE(2016,05,01)}, TRUE(), FALSE()), you need to replace/add the public holidays that correspond to the holidays in your country.https://drive.google.com/file/d/1VsxGNFc-_ayIBurnBHB4hNJ4b_4827OM/view?usp=sharing
Let me know
- bbqq0215 years agoFrequent Visitor
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.