Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

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

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

miguelroman_0-1690965268060.png

 

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!

0 REPLIES 0

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.