Forum Discussion

msimmonsmcse's avatar
msimmonsmcse
Helper I
4 years ago
Solved

Counting working days

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 ...
Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    4 years ago

    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's avatar
AlexisOlson
Super User
4 years ago

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 ) )