Difference between 2 days Exclude Weekend and Holidays

[Anonymous]

i have a calender table and Holiday Table , I do not have a relationship with Calendar Table and Hoiday Table,  i need DATEDIFF between two dates, and within the function exclude weekends and holidays between these two dates. 

Calendar table code

Calendar =

ADDCOLUMNS(

CALENDARAUTO(),

"Year", Year([Date]),

"Month", EOMONTH([Date],-1)+1,

"WorkingDay", IF(WEEKDAY([Date],2)>5,1)

)

i have done Weekend but i Don't know how to do Holiday,  How can i add holiday list in Calender table 

here is the result that i get from the formula,

[Anonymous]

Hi @Anonymous 

This is easier than you think 🙂 Once you have your calendar and holidays, just based on the holidays mark the days in the calendar that are in the holidays table. This will be a calculated column of bool type. TRUE - holiday, FALSE - ordinary day. Once you've got the flag column (hidden), you can now easily author a measure that will use the flag to give you what you want.

Best

D

[harshnathani]

HI @Anonymous ,

Have you tried this.

https://www.youtube.com/watch?v=v9s0PPLU0n8

https://community.powerbi.com/t5/Desktop/Public-Holiday-Script/td-p/178341

https://community.powerbi.com/t5/Desktop/Matching-dates-to-a-holiday-table/td-p/430803

https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Anonymous]

Hi @harshnathani   

i have created Holiday table and calender table then how can i merge this two table,   in query editor calenrder table is not showing

[harshnathani]

Hi @Anonymous ,

Follow these steps to create a holiday Column.

Post this I guess you will have 2 Column, Weekend and Holidays.

Then Create a Column to merge these

IF( AND( Weekdays = Saturday, Weekday = Sunday, IsHoliday = TRUE ), 0, 1 )

Regards,

Harsh Nathani

[Anonymous]

Hi @harshnathani  Thanks for your reply

I have done WorkingDays , but  Holidays calculation not working 

I have attached my PBIX file  Click here    Could you Please check?

Thanks & Regards,

Rajesh

[mohammedadnant]

Hi @Anonymous 

Good day,

pls add 2 new columns as below

*******************************************************************************************************

Holiday =
VAR _HolidayCount =
    CALCULATE(
        COUNTROWS(ftCalendar),
        DATESBETWEEN(ftCalendar[Date],Finallist[RequestReceivedDate],Finallist[RequestClosedDate]-1),
        not ISBLANK(ftCalendar[Holiday])
        --ftCalendar[WorkingDay] = 1
    )
RETURN
COALESCE( _HolidayCount,0)
*******************************************************************************************************
w/o holiday only workingday = Finallist[Working Days]-Finallist[Holiday]

  *******************************************************************************************************

Pls mark this answer as complete if solves your issue

Thanks & Regards,

Mohammed Adnan

Learn Power BI For Free.. click below image.

[harshnathani]

Hi @Anonymous ,

https://drive.google.com/file/d/117v9j_c4WuaeJjCmj9RaSpmBAvkZzO9U/view?usp=sharing

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Anonymous]

Hi @harshnathani 

Thanks for your reply,  i'm not able to open that file which you attached

Could you Please share the steps please,  sorry for the inconvenience

Thanks 

Rajesh

[harshnathani · Accepted solution]

HI @Anonymous ,

Create 2 calculated column

Holiday = LOOKUPVALUE(Holidays[HolidayDate],Holidays[HolidayDate],ftCalendar[Date])

Working or Not = SWITCH(

TRUE(),

ftCalendar[DateIsWorkingDay] = TRUE() && ftCalendar[Holiday] = BLANK(),"Working Day", "Non Working Day")

Regards,

Harsh Nathani