Networking days in each month considering holiday and weekends

[ashik1992]

Hi 

need a help to create networking days in each month .

I have master table in which a column called "workdate". work date starts from 01-01-2021 to 12-31-2021.

I have another table which having the holidays details. i need to find the networking days of each month considering weekends and holidays. please help me.

New Year's Day	Jan 01, 2021 (Fri)
Republic Day	Jan 26, 2021 (Tue)
Ramzan Id/Eid-ul-Fitar	May 14, 2021 (Fri)
Diwali / Deepavali	Nov 04, 2021 (Thu)
Christmas Eve	Dec 24, 2021 (Fri)

[v-angzheng-msft · Accepted solution]

Hi, @ashik1992 

To create 2 calculated columns(holiday column, weekNum column)in master table.

holiday = RELATED(Holiday[holiday])

dayNum = WEEKDAY('MasterTable'[Date],2)

then create 2 measures (isNetworkday, Count).

_isNetworkday = 
var _if=IF(MAX('MasterTable'[holiday])=BLANK()&&NOT(MAX(MasterTable[dayNum])in{6,7}),1)
return _if

_Count = 
var _monthTable=FILTER(ALL('MasterTable'),'MasterTable'[Month]=MAX('MasterTable'[Month]))
var _sum=SUMX(_monthTable,[_isNetworkday])
var _a=SUMMARIZE('MasterTable',[Month],"value",_sum)
return SUMX(_a,[value])

so the result would be like this:

Please refer to the attachment below for details

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Greg_Deckler]

@ashik1992 I created this:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/td-p/367362

I have an improved version in my book, DAX Cookbook, recipe 10 in chapter 2. gdeckler/DAXCookbook (github.com)