Count # of working days per month based on starting date of an employee

[kristel_tulio]

Hi,

I would like to ask for help. I have 2 tables (Date table & UserMap table). In user map I have name of managers and their hire date.

I would like to count the number of working days each month starting on their hire date.

[kristel_tulio · Accepted solution]

Hi @daXtreme 

Thank you for that, I just have a question It seems the count is accumulating on my end.

[v-yinliw-msft · Accepted solution]

Hi @kristel_tulio ,

You can try the following methods.

Measure:

Total Days M = 
VAR N1 =
    CALCULATE (
        COUNT ( 'Date'[Workday] ),
        FILTER (
            'Date',
            [Date] >= SELECTEDVALUE ( 'Table'[Date of entry] )
                && [Date] <= SELECTEDVALUE ( 'Table'[Date of separation] )
                && [Workday] = 1
        )
    )
VAR N2 =
    CALCULATE (
        COUNT ( 'Date'[Workday] ),
        FILTER (
            'Date',
            [Date] >= SELECTEDVALUE ( 'Table'[Date of entry] )
                && [Date] <= TODAY ()
                && [Workday] = 1
        )
    )
RETURN
    IF ( SELECTEDVALUE ( 'Table'[Date of separation] ) = BLANK (), N2, N1 )

employee ID	Date of entry	Date of separation
1	2/1/2022	5/1/2022
2	3/1/2022
3	4/1/2022
4	5/1/2022	8/31/2022
5	6/1/2022
6	7/1/2022

Is this the result you expect?

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kristel_tulio]

Hi @v-yinliw-msft 

It's great! , This one works for me. Thanks a lot

[kristel_tulio · Accepted solution]

Hi @daXtreme 

Thank you for that, I just have a question It seems the count is accumulating on my end.

[daXtreme]

Well, since your code works differently to mine... your model/code is not equivalent. Find the difference and correct. That's all I can tell you.

[kristel_tulio]

Thank you @daXtreme 

[daXtreme]

Solution attached...