Forum Discussion

bbqq021's avatar
bbqq021
Frequent Visitor
5 years ago
Solved

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 functi...
  • v-luwang-msft's avatar
    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
    day3
    

     

    AVERAGE = Original[Resource]/Original[difforder1]

     

    Final you will get the below:

     

    Wish it is helpful for you!

     

    Best Regards

    Lucien