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

DateWeekdayWeekday Name
1/1/20227Saturday
1/2/20221Sunday
1/3/20222Monday

 

Member Table:

NameNon Working Day
John Smithnull
Jessica Rabbit2
Homer Simpson6
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.

2 Replies

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