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

Anonymous — Wed, 02 Aug 2023 08:36:16 GMT

Good morning everyone.

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

Worker ID
Teams
First day of absence
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!

topic Calculating the days per mont of a range date and aggregating it. in DAX Commands and Tips

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