Counting working days

[msimmonsmcse]

I have 2 tables, a Member table and a Date table. Some members have non-working days. I want to calculate the number of non working days for any filtered date range for each member. I have a Weekday column in the Date table that numbers each Day value 1-7, and a Non Working Days value in the Member table that identifies the Weekday value of a each members non work day. Members without non work days have null vallue. That value is a Conditional Column. What is the DAX to accomplish this in a measure?

Date Table:

Date	Weekday	Weekday Name
1/1/2022	7	Saturday
1/2/2022	1	Sunday
1/3/2022	2	Monday

Member Table:

Name	Non Working Day
John Smith	null
Jessica Rabbit	2
Homer Simpson	6

[v-angzheng-msft · Accepted solution]

Hi, @msimmonsmcse 

Try to create a measure like this:

Measure = 
var _TotalDays=
CALCULATE(
    COUNT('DataTable'[WeekDay]),
    REMOVEFILTERS('Member Table'[Name])
)
var _non_workingDays=
CALCULATE(
    COUNT('DataTable'[WeekDay]),
    FILTER('DataTable','DataTable'[WeekDay] in VALUES('Member Table'[Non Working Day])))
var _result=_TotalDays-_non_workingDays
return _result

Result:

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.

[AlexisOlson]

I think you can write the base logic along these lines:

Non-Working Day Count =
VAR NonWorkDay = SELECTEDVALUE ( Member[Non Working Day] )
RETURN
    COUNTROWS ( FILTER ( DateTable, DateTable[Weekday] <> NonWorkDay ) )