DAX, Disaggregate PLAN data - Excluding weekends and Public holidays

[bbqq021]

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:

1. Count the working days between start and end
2. Divide the resource in the row by the number of working days
3. 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 

[v-luwang-msft · Accepted solution]

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

[bbqq021]

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

[bbqq021]

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.

[aj1973]

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

[Ashish_Mathur]

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.