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.

v-angzheng-msft's avatar
v-angzheng-msft
Community Support
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.