Calculating the days per mont of a range date and aggregating it.

[Anonymous]

Good morning everyone.

I am currently having a problem related to some dates. Right now I have a table that contain multiple columns:

1. Worker ID
2. Teams
3. First day of absence
4. Last day of absence

What I need is to distribute by Month the number of days it has been absent (in each month). I managed to do it with this code and using a basic date table:

Days of leave per month Numerador = 
VAR StartDayLeave = VALUE( SELECTEDVALUE( Leave_Active_2023[First_Day_of_Leave] ) )
VAR LastDayLeave = VALUE( SELECTEDVALUE( Leave_Active_2023[Last_Day_of_Leave_-_Actual] ) )
VAR MinDateInContext = VALUE( MIN( 'Dates'[Date] ) )
VAR MaxDateInContext = VALUE( MAX( 'Dates'[Date] ) )

Return
IF(AND( LastDayLeave > MinDateInContext, LastDayLeave < MaxDateInContext ),  MaxDateInContext  - LastDayLeave + 1,   
    IF( AND( StartDayLeave < MinDateInContext, LastDayLeave > MinDateInContext ) ,
        MaxDateInContext  - MinDateInContext + 1,
                IF( AND( AND( StartDayLeave > MinDateInContext, StartDayLeave < MaxDateInContext ), LastDayLeave > MinDateInContext ),
                     MIN( LastDayLeave, MaxDateInContext + 1 ) - StartDayLeave, 
                        BLANK() ) ))

 This formula gives me the next table

The problem comes when I try to aggregate the results by teams. I understand that the problem comes due to the MIN and MAX in the formula, seen that it takes the min value of the whole team, and the max value of the whole team. However, I cannot manage to find an answer. Does someone have any idea on how to solve this problem?

Thank you very much!