Forum Discussion
DAX, Disaggregate PLAN data - Excluding weekends and Public holidays
Hello Everyone
I thought i found the solution to my problem here posted by Vvelarde Vvelarde-altern in answer to Martin-Prague Martin-Prague2's problem however i am looking for additional functionality and i cannot create it myself.
The problem here is that individual task starts on a date and the end date is some time in the future meaning you have no information for the days between the start and end. The solution was to spread the resource evenly over the days between start and end. This is fine however my original plan assumes weekends and public holidays are not worked and so i want to reflect this. My steps would be:
- Count the working days between start and end
- Divide the resource in the row by the number of working days
- Apply this number to working days between start and end.
Here is some sample data:
Task ID Start date End date Resource
888405244 01.01.2016 31.12.2016 726190
888405244 01.01.2017 31.12.2017 890145
873145821 01.01.2016 31.12.2016 726190
873145821 01.01.2017 31.12.2017 890145
873145818 01.01.2016 31.12.2016 726190
and here is a sample date table
Date,Weekday,Holiday
01/01/2016,5,FALSE
02/01/2016,6,FALSE
03/01/2016,7,FALSE
04/01/2016,1,FALSE
05/01/2016,2,FALSE
06/01/2016,3,FALSE
07/01/2016,4,FALSE
08/01/2016,5,FALSE
09/01/2016,6,FALSE
10/01/2016,7,FALSE
11/01/2016,1,FALSE
12/01/2016,2,FALSE
13/01/2016,3,FALSE
14/01/2016,4,FALSE
15/01/2016,5,FALSE
16/01/2016,6,FALSE
17/01/2016,7,FALSE
18/01/2016,1,FALSE
19/01/2016,2,FALSE
20/01/2016,3,FALSE
21/01/2016,4,TRUE
22/01/2016,5,FALSE
23/01/2016,6,FALSE
24/01/2016,7,FALSE
25/01/2016,1,FALSE
26/01/2016,2,FALSE
27/01/2016,3,FALSE
Appoligise if i have posted this twice however the original solution was marked solved and so i didn't expect it to get coverage. Pointers would be appreciated.
Thanks
Will
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
6 Replies
- Ashish_Mathur
Super User
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.
- aj1973
Community Champion
Hi bbqq021
Attached a Pibx file on how to get number of days between start and end dates using 3 different ways
https://drive.google.com/file/d/1bikJXNbDHW4k-D1rGDXCWlUTPYTuHgtD/view?usp=sharing
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
- aj1973
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
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.
- v-luwang-msft
Community Support
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
