Working Days Off DAX Formula

[Anonymous]

Hello everyone, I hope you're doing well.

I'm having an issue with a calculation I'm trying to make to calculate the days of leave taken by professionals.

Previously, I was helped with the following calculation that answered the question:

"If a person has a leave that starts on 01/25 and ends on 02/10, it only brings the 6 days of January and the 10 of February."

The calculation was as follows:

Days count = 
VAR MaxDate = MAX(Calendario[Fecha])
VAR MinDate = MIN(Calendario[Fecha])

RETURN 
    SUMX
    (
        Licencias,
        DATEDIFF
        ( 
            MAX( Licencias[INICIO], MinDate ),
            MIN( Licencias[FIN], MaxDate),
            DAY
        ) + 1
    )

But I have a problem, since my filter is related to the start of the leave, when I apply the filter to the next month, it no longer counts the January leaves. That is to say, the calculation won't bring the 10 corresponding days of February because it filters the January leaves and doesn't take them into account.

[OwenAuger · Accepted solution]

Hi @Anonymous 

From what you've described, the issue appears to be that you have a relationship between columns Calendario[Fecha] (Date column of Calendar table) and Licencias[INICIO] (the start date of the leave).

As you've described, this means that when a filter is applied to the Calendario table, your measure will only count days of leave if the start date falls within the filtered period.

The general solution I would recommend is to use DAX to removethe Date filter for the purpose of the SUMX(...) calculation.

Also, you should an additional filter to ensure only rows of Licencias that intersect the filtered date range are included, otherwise you would get odd results for rows that don't intersect the filtered date range at all.

Also, I personally prefer a simple subtraction rather than DATEDIFF.

Something like this:

Days count =
VAR MaxDate =
    MAX ( Calendario[Fecha] )
VAR MinDate =
    MIN ( Calendario[Fecha] )
RETURN
    CALCULATE (
        SUMX (
            Licencias,
            VAR INICIO_Adjusted =
                MAX (
                    Licencias[INICIO],
                    MinDate
                )
            VAR FIN_Adjusted =
                MIN (
                    Licencias[FIN],
                    MaxDate
                )
            RETURN
                FIN_Adjusted - INICIO_Adjusted + 1
        ),
        -- Remove filter on Calendario that would filter Licencias[INICIO]
        ALL ( Calendario ), 
        -- Only rows where INICIO is on/before MaxDate
        Licencias[INICIO] <= MaxDate,
        -- Only rows where FIN is on/after MinDate
        Licencias[FIN] >= MinDate
    )

Does this work for you?

[Anonymous]

Hi @OwenAuger 

Thank you very much for helping me with this DAX measure, this correction has resolved the question and now I can make more accurate calculations.

I just had to make one final correction, as the result of the measure only provided a fixed date.

the correction was: 

Days count = 
VAR MaxDate =
    MAX ( Calendario[Fecha] )
VAR MinDate =
    MIN ( Calendario[Fecha] )
RETURN
    CALCULATE (
        SUMX (
            Licencias,
            VAR INICIO_Adjusted =
                MAX (
                    Licencias[INICIO],
                    MinDate
                )
            VAR FIN_Adjusted =
                MIN (
                    Licencias[FIN],
                    MaxDate
                )
            RETURN
                   DATEDIFF(INICIO_Adjusted, FIN_Adjusted, DAY) + 1
-- add DATEDIFF function --  
        ),
        -- Remove filter on Calendario that would filter Licencias[INICIO]
        ALL ( Calendario ), 
        -- Only rows where INICIO is on/before MaxDate
        Licencias[INICIO] <= MaxDate,
        -- Only rows where FIN is on/after MinDate
        Licencias[FIN] >= MinDate
    )