Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

How exclude holidays from networking days

Hello Community,

 

I have created networkdays between two days (it includes holidays and excludes weekends) by using this:

NetWorkDaysEND =
VAR Calendar1 = CALENDAR(MIN('Query1'[END]),MAX('Query1'[FYEND]))
VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekDay",WEEKDAY([Date],2))
RETURN
COUNTX(FILTER(Calendar2,[WeekDay]<=7),[Date])- COUNTX(FILTER(Calendar2,[WeekDay]>=6),[Date])
 
But now i have holidays table with DAYName, Date, Holiday Columns.... So, i want to exclude holidays also 
Can you guys please help me out from this
 
Thank you in advance
 
Regards,
B V S S
1 ACCEPTED SOLUTION
v-lid-msft
Community Support
Community Support

Hi @Anonymous ,

 

we can create such a measure to meet your requirement.

 

NetWorkDaysENDExcludeHoliday = 
VAR Calendar1 =
    CALENDAR ( MIN ( 'Query1'[END] ), MAX ( 'Query1'[FYEND] ) )
VAR Calendar2 =
    ADDCOLUMNS ( Calendar1, "WeekDay", WEEKDAY ( [Date], 2 ) )
RETURN
    SUMX (
        FILTER ( Calendar2, [WeekDay] <= 5 ),
        IF ( CONTAINS ( holidays, 'holidays'[Date], [date] ), 0, 1 )
    )

Holiday.PNG

 

 

BTW, pbix as attached.

 

Best regards,

 

Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-lid-msft
Community Support
Community Support

Hi @Anonymous ,

 

we can create such a measure to meet your requirement.

 

NetWorkDaysENDExcludeHoliday = 
VAR Calendar1 =
    CALENDAR ( MIN ( 'Query1'[END] ), MAX ( 'Query1'[FYEND] ) )
VAR Calendar2 =
    ADDCOLUMNS ( Calendar1, "WeekDay", WEEKDAY ( [Date], 2 ) )
RETURN
    SUMX (
        FILTER ( Calendar2, [WeekDay] <= 5 ),
        IF ( CONTAINS ( holidays, 'holidays'[Date], [date] ), 0, 1 )
    )

Holiday.PNG

 

 

BTW, pbix as attached.

 

Best regards,

 

Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi @v-lid-msft ,

 

Thank you for your solution.. It's working fine for my data

Anonymous
Not applicable

Hello guys,

 

Any Suggestions Please... 

 

Thank you in advance

 

 

Regards,

B V S S

Hello @Anonymous 

I have attached a sample file where my Dates table has a column "IsWorkingDay" that is = FALSE on weekends and holidays.  Using that and the dates you are comparing you can just do a count of rows with some filtering.

WorkingDays = 
VAR LowEnd =
    FIRSTDATE ( 'Table'[StartDate] )
VAR HighEnd =
    LASTDATE ( 'Table'[EndDate] )
RETURN
    CALCULATE (
        COUNTROWS ( Dates ),
        Dates[Date] >= LowEnd && Dates[Date] <= HighEnd,
        Dates[IsWorkingDay] = TRUE
    )
Anonymous
Not applicable

Hi @jdbuchanan71 ,

 

Thank you for your response.. I tried but it's not working properly for my case. May be some other cases it will work

 

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.