Calculate days worked according to a filter

[Syndicate_Admin]

Hello

I have a Fact with employees with their period start date and end period date, and I need to calculate according to a date filter of a calendar dimension. How many days I work in the range of dates selected by the user. For example.

consultation period	1-9-22	15-9-22
			Septiempre
	Income	Retreat	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	days x entered	x days withdrawn
Person 1	9-9-22			1	1	1	1	1	1	1	1	1	1	1	1	1	1	14
Person 2	15-01-22	12-09-22	1	1	1	1	1	1	1	1	1	1	1	1					12
Person 3	6-9-22	12-9-22						1	1	1	1	1	1						6
Total	14	18

I have the following metric.

Days Worked =

Where _MinDate =

MIN( 'Calendar.HHRR Dates'[Date] )

Where _MaxDate =

.MAX( 'Calendar.HHRR Dates'[Date] )

Where _ResultDateMax=

CALCULATE(

.MAX('HHRR Movements'[End_Date_Period]),

'HHRR Movements'[Active]=TRUE

,'HHRR Movements'[End_Date_Period] >= _MinDate

,'HHRR Movements'[End_Date_Period] <= _MaxDate

)

Where Result =

DATEDIFF(_MinDate,_ResultDateMax,DAY)+1

Return

Result

But I should mark from the 6 that I enter until the 12 that retires, that would be the first scenario, the other scenario is that I may have entered on a date earlier than the selected range in that case I should be counted from the initial date selected.

These are the metrics I have so far to calculate income and withdrawals

Income=

VAR MinDate =
MIN( 'Calendar.HHRR Dates'[Date] )
VAR MaxDate =
MAX( 'Calendar.HHRR Dates'[Date] )
VAR Result =
CALCULATE(
DISTINCTCOUNT( 'HHRR Movements'[AlternateCode] ),
'HHRR Movements'[Active] = TRUE
,'HHRR Movements'[Start_Date_Period] >= MinDate
,'HHRR Movements'[Start_Date_Period] <= MaxDate
)
RETURN
Result

Withdrawals=

VAR MinDate =
MIN( 'Calendar.HHRR Dates'[Date] )
VAR MaxDate =
MAX( 'Calendar.HHRR Dates'[Date] )
VAR Result =
CALCULATE(
DISTINCTCOUNT( 'HHRR Movements'[AlternateCode] ),
'HHRR Movements'[Active] = TRUE
,'HHRR Movements'[End_Date_Period] >= MinDate
,'HHRR Movements'[End_Date_Period] <= MaxDate
)
RETURN
Result

Any help will be very useful. Thanks a lot.

[lbendlin]

You can use your own calendar table or you can use the NETWORKDAYS() function to filter an existing calendar. In any case that calendar table likely needs to be disconnected from the rest of the data model, so that you can do a cross join with all the employees.  Then you can write a measure that calculates if a particular data should be considered a work day for that employee.