topic Re: Difference between 2 days Exclude Weekend and Holidays in DAX Commands and Tips

Difference between 2 days Exclude Weekend and Holidays

Anonymous — Thu, 25 Jun 2020 06:58:11 GMT

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,

Re: Difference between 2 days Exclude Weekend and Holidays

Anonymous — Thu, 25 Jun 2020 07:04:14 GMT

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)

Re: Difference between 2 days Exclude Weekend and Holidays

Anonymous — Thu, 25 Jun 2020 10:51:21 GMT

Hi Anonymous

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

Re: Difference between 2 days Exclude Weekend and Holidays

Anonymous — Thu, 25 Jun 2020 10:56:44 GMT

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

Re: Difference between 2 days Exclude Weekend and Holidays

Anonymous — Thu, 25 Jun 2020 12:18:26 GMT

Hi Anonymous 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

Re: Difference between 2 days Exclude Weekend and Holidays

Anonymous — Thu, 25 Jun 2020 12:57:05 GMT

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)

Re: Difference between 2 days Exclude Weekend and Holidays

Anonymous — Thu, 25 Jun 2020 13:20:12 GMT

Hi Anonymous

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

Re: Difference between 2 days Exclude Weekend and Holidays

Anonymous — Thu, 25 Jun 2020 13:34:23 GMT

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

Re: Difference between 2 days Exclude Weekend and Holidays

Anonymous — Fri, 26 Jun 2020 10:16:50 GMT

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

Re: Difference between 2 days Exclude Weekend and Holidays

mohammedadnant — Sat, 27 Jun 2020 05:23:18 GMT

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.