Calculate days difference between dates filtered by year

[Mano82]

Hi,

as per subject I need to calculate the number of days between two dates. It may happen that the two dates are crossing the year. In the dashboard visualization I may filter the year with a combo box. The goal is that the number of days is calculated according to the filtered year.

Example.

Date Start = 30/12/2024

Date End = 02/01/2025

Date filter --> ALL --> DAYS = 4

Date filter --> 2024 --> DAYS = 2

Here's the formula I've got so far. It is bit more complicated because it takes into account other requirements. 

Days = 
VAR MaxEventCertNo = 
    CALCULATE(
        MAX(Incidenti_giorni_assenza[Event cert No]),
        ALLEXCEPT(Incidenti_giorni_assenza, Incidenti_giorni_assenza[Event No])
    )

VAR IncidentDate = RELATED(Incidenti_master[Incident date])

VAR SelectedEndDate =
    IF(
        Incidenti_giorni_assenza[Confirmed Date Real] = 1,
        Incidenti_giorni_assenza[Diag_ End Date Real],
        Incidenti_giorni_assenza[Diag_ End Date Planned]
    )

-- Determina la data massima dell'anno selezionato nel filtro
VAR MaxFilteredYear = 
    MAX( 'Calendario'[Anno] ) -- Prende l'anno massimo dal filtro

VAR MaxFilteredDate = 
    DATE( MaxFilteredYear, 12, 31 ) -- Ultimo giorno dell'anno selezionato

-- Limita la data finale al 31/12 dell'anno selezionato se necessario
VAR EndDate =
    IF( SelectedEndDate > MaxFilteredDate, MaxFilteredDate, SelectedEndDate )

-- Calcola la differenza in giorni considerando gli estremi
VAR DaysDifference = DATEDIFF(IncidentDate, EndDate, DAY) + 1

-- Restituisci il valore solo se è il record con Event Cert No più alto
RETURN 
    IF(Incidenti_giorni_assenza[Event cert No] = MaxEventCertNo, DaysDifference, BLANK())

However the problem is that it works for the most of it but it doesn't work splitting the days by year.

Example:

Incident date (start date) = 26/11/2024
Planned/Real date (end date) = 14/02/2025
Always give a result of 81. If filtered by year 2024 it should give a result of 36. If filtered by year 2025 it should give 45 (ok maybe this won't happen because the master record is registered in 2024 and it will be filtered out).

Any hint?

Thanks in advance

[Anonymous]

Thanks for the reply from lbendlin, please allow me to provide another insight.

Hi @Mano82 ,

Based on the information you provided, 'Days' appears to be a calculated column.

If so, then it's to be expected that the value of 'Days' remains unchanged after the slicer changes the year.

This is because:

1. The value of the Calculated column is calculated during the data refresh, and its value is determined after the calculation is complete and is not affected by the interaction of other visuals in the report.
2. Measures are calculated dynamically as the visual executes the query and are affected by the interaction of other visuals in the report.

Calculated Columns and Measures in DAX - SQLBI

Therefore, you need to create a measure to show the difference in days between dates.

Please refers to the following steps.

The dataset model is as follows.

Use the following measure to calculate the difference in days between dates.

Days_Measure = 
VAR MaxEventCertNo = 
    CALCULATE(
        MAX(Incidenti_giorni_assenza[Event cert No]),
        ALLEXCEPT(Incidenti_giorni_assenza, Incidenti_giorni_assenza[Event No])
    )

VAR IncidentDate = MAX(Incidenti_master[Incident date])

VAR SelectedEndDate =
    IF(
        MAX(Incidenti_giorni_assenza[Confirmed Date Real]) = 1,
        MAX(Incidenti_giorni_assenza[Diag_ End Date Real]),
        MAX(Incidenti_giorni_assenza[Diag_ End Date Planned])
    )

-- Determina la data massima dell'anno selezionato nel filtro
VAR MaxFilteredYear = 
    MAX( 'Calendario'[Anno] ) -- Prende l'anno massimo dal filtro

VAR MaxFilteredDate = 
    DATE( MaxFilteredYear, 12, 31 ) -- Ultimo giorno dell'anno selezionato

-- Limita la data finale al 31/12 dell'anno selezionato se necessario
VAR EndDate =
    IF( SelectedEndDate > MaxFilteredDate, MaxFilteredDate, SelectedEndDate )

-- Calcola la differenza in giorni considerando gli estremi
VAR DaysDifference = DATEDIFF(IncidentDate, EndDate, DAY) + 1

-- Restituisci il valore solo se è il record con Event Cert No più alto
RETURN 
    IF(MAX(Incidenti_giorni_assenza[Event cert No]) = MaxEventCertNo, DaysDifference, BLANK())

The final result is as follows. Hopefully it will meet your needs.

Please see the attached pbix for reference.

Best Regards,
Dengliang Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mano82]

Hi @Anonymous , thank you so much for your time and your help!

Actually I knew the difference between calculated column and measure but somehow I didn't think it was needed here!

The calculation works as expected, however I have another added request that I don't know how to solve actually...maybe you can help a bit more.

The Event No is related to the Incident Date that is linked to the calendar filtered year. Considering now as example the Event No INC24-00038, without filtering the year, the result is 102 days.

If now I filter 2024 the Days calculation is correct (36).

However if I filter 2025 I should have a result of 66 but instead I have no record at all.

I perfectly understand that it won't show because the Event, that linked to year 2024, is being filtered out...however I wonder if there is someway to make it work.  

Thank you

[Anonymous · Accepted solution]

Hi @Mano82 ,

Please try the following measure.

Days_Measure = 
VAR MaxEventCertNo = 
    CALCULATE(
        MAX(Incidenti_giorni_assenza[Event cert No]),
        ALLEXCEPT(Incidenti_giorni_assenza, Incidenti_giorni_assenza[Event No])
    )

VAR IncidentDate = MAX(Incidenti_master[Incident date])

VAR SelectedEndDate =
    IF(
        MAX(Incidenti_giorni_assenza[Confirmed Date Real]) = 1,
        MAX(Incidenti_giorni_assenza[Diag_ End Date Real]),
        MAX(Incidenti_giorni_assenza[Diag_ End Date Planned])
    )

-- Determina la data massima dell'anno selezionato nel filtro
VAR MaxFilteredYear = 
    MAX( 'Calendario'[Anno] ) -- Prende l'anno massimo dal filtro

VAR MaxFilteredDate = 
    DATE( MaxFilteredYear, 12, 31 ) -- Ultimo giorno dell'anno selezionato

-- Limita la data finale al 31/12 dell'anno selezionato se necessario
VAR EndDate =
    IF( SelectedEndDate > MaxFilteredDate, MaxFilteredDate, SelectedEndDate )

-- Calcola la differenza in giorni considerando gli estremi
VAR DaysDifference = 
IF( //Determines if the year slicer filters any years.
    ISFILTERED('Calendario'[anno]),
    IF(MaxFilteredYear > YEAR(IncidentDate) && MaxFilteredYear = YEAR(EndDate), 
    DATEDIFF(DATE(YEAR(EndDate),1,1), EndDate,DAY)+1,
    DATEDIFF(IncidentDate, EndDate, DAY) + 1
    ),
    DATEDIFF(IncidentDate, EndDate, DAY) + 1
)

-- Restituisci il valore solo se è il record con Event Cert No più alto
RETURN 
    IF(MAX(Incidenti_giorni_assenza[Event cert No]) = MaxEventCertNo, DaysDifference, BLANK())

Shows all days when the year slicer is not filtering any years.

When the year in the year slicer is greater than 'IncidentDate' and is equal to the year of 'EndDate', shows the difference in the days from the first day of the year of 'EndDate' to 'EndDate'.

In other cases, the difference in days is shown normally.

Please see the attached pbix for reference.

Best Regards,
Dengliang Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Read about INTERSECT.  Much easier to use.