Forum Discussion

Mano82's avatar
Mano82
New Member
1 year ago
Solved

Calculate days difference between dates filtered by year

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...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

Mano82's avatar
Mano82
New Member
1 year ago

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's avatar
Anonymous
Not applicable
1 year ago

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.