topic Re: Count # of working days per month based on starting date of an employee in DAX Commands and Tips

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

kristel_tulio — Wed, 07 Sep 2022 06:54:53 GMT

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.

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

daXtreme — Wed, 07 Sep 2022 09:05:47 GMT

Solution attached...

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

kristel_tulio — Thu, 08 Sep 2022 00:49:18 GMT

Hi daXtreme

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

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

daXtreme — Thu, 08 Sep 2022 07:52:52 GMT

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.

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

kristel_tulio — Thu, 08 Sep 2022 07:57:31 GMT

Thank you daXtreme

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

v-yinliw-msft — Thu, 08 Sep 2022 09:26:06 GMT

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.

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

kristel_tulio — Fri, 09 Sep 2022 00:10:02 GMT

Hi v-yinliw-msft

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