Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Good morning everyone.
I am currently having a problem related to some dates. Right now I have a table that contain multiple columns:
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!
User | Count |
---|---|
56 | |
22 | |
19 | |
16 | |
11 |
User | Count |
---|---|
85 | |
54 | |
39 | |
21 | |
18 |