Calculating maximum time between two records

[Anonymous]

Hello,

I have a dashboard that I use for statistics on incident reports.

I use a measure to calculate the working days between today and the last incident posted in my database.

Here's the measure :

max(CALCULATE(
COUNTROWS(Dates),FILTER(Dates,WEEKDAY(Dates[Date],2)<6 ), DATESBETWEEN(Dates[Date],
MAXX(filter(all(Incident),Incident[TypeAccident]="AT" ),Incident[DateAndTime]),TODAY()))-2,0)

This measure works fine for the difference between the last accident and today.

I want to be abble to show the record of days without incident (the longest time between two incident).

You can find a sample data here.

Thanks for your help.

Philippe.

[timalbers]

Hi @Anonymous 

try something like this:

Measure =
VAR _incidents =
    ADDCOLUMNS(
        FILTER( Incident, Incident[TypeAccident] = "AT" ),
        "prev_incident",
        CALCULATE(
            MAX( Incident[DateAndTime] ),
            FILTER(
                ALL( Incident ),
                Incident[DateAndTime] < EARLIER( Incident[DateAndTime] ) &&
                Incident[TypeAccident] = "AT"
            )
        )
    )

VAR _max =
    MAXX(
        _incidents,
        COUNTROWS(
            FILTER(
                Dates,
                Dates[Date] > _incidents[prev_incident] &&
                Dates[Date] <= _incidents[DateAndTime] &&
                WEEKDAY( Dates[Date], 2 ) < 6
            )
        )
    )

RETURN _max

 
1. The _incidents variable contains each incident and the date of the previous incident.
2. The _max variable finds the highest count of working days between two incidents.

[Anonymous]

Hi @timalbers , your solution worked witth a little tweek on the MAXX measure.

Here's the final measure :

Measure = 
VAR _incidents =
    ADDCOLUMNS(
        FILTER( Incident, Incident[TypeAccident] = "AT" ),
        "prev_incident",
        CALCULATE(
            MAX( Incident[DateAndTime] ),
            FILTER(
                ALL( Incident ),
                Incident[DateAndTime] < EARLIER( Incident[DateAndTime] ) &&
                Incident[TypeAccident] = "AT"
            )
        )
    )

VAR _max =
    MAXX(
        _incidents,
        COUNTROWS(
            FILTER(
                Dates,
                Dates[Date] > [prev_incident] &&
                Dates[Date] <= [DateAndTime] &&
                WEEKDAY( Dates[Date], 2 ) < 6
            )
        ) 
    )

RETURN _max

I would like to add a small modification.

For the moment, it shows the highest count of working days between two incidents.

But when the highest count is between the last incident and today, it still show the highest count of working days between two incident.

Do you think it's possible to add this in the measure.

Again thanks for your help and have a nice day.

BR,

Philippe 

[timalbers · Accepted solution]

Yea my bad, as _incidents is a virtual table inside the same expression, DAX expects column references without the table name.

If you want to also include the time between the last incident and today, we will have to add some more variables inside the measure. Just append this to the existing variables and replace the RETURN row:

VAR last_incident = MAXX( FILTER( Incident, Incident[TypeAccident] = "AT" ), Incident[DateAndTime] )

VAR last_vs_today =
    COUNTROWS(
        FILTER(
            Dates,
            Dates[Date] > last_incident &&
            Dates[Date] <= TODAY() &&
            WEEKDAY( Dates[Date], 2 ) < 6
        )
    )

RETURN MAX( _max, last_vs_today )

[Anonymous]

@timalbers  Sorry to bother you again, but when I moved your DAX to my real file, it's was not working anymore.

In the Sample file, it seems to work fine, but in my real file no.

The big difference between the two file is that I have much more record in the real file, and also I have different sites (for each factory) and i have slicers for the different sites.

Here's the DAX in my real file :

Record de jours sans accidents = 
VAR _incidents =
    ADDCOLUMNS(
        FILTER( 'Accidents du travail', 'Accidents du travail'[Type d'accident du travail] = "AT avec arrêt" ),
        "prev_incident",
        CALCULATE(
            MAX( 'Accidents du travail'[Date et heure.1]),
            FILTER(
                ALL( 'Accidents du travail' ),
                'Accidents du travail'[Date et heure.1] < EARLIER( 'Accidents du travail'[Date et heure.1] ) &&
                'Accidents du travail'[Type d'accident du travail] = "AT avec arrêt"
            )
        )
    )

VAR _max =
    MAXX(
        _incidents,
        COUNTROWS(
            FILTER(
                'Calendar',
                'Calendar'[Date] > [prev_incident] &&
                'Calendar'[Date] <= [Date et heure.1] &&
                WEEKDAY( 'Calendar'[Date], 2 ) < 6
            )
        ) 
    )

VAR last_incident = MAXX( FILTER( 'Accidents du travail', 'Accidents du travail'[Type d'accident du travail] = "AT avec arrêt" ), 'Accidents du travail'[Date et heure.1] )

VAR last_vs_today =
    COUNTROWS(
        FILTER(
            'Calendar',
            'Calendar'[Date] > last_incident &&
            'Calendar'[Date] <= TODAY() &&
            WEEKDAY( 'Calendar'[Date], 2 ) < 6
        )
    )-1

RETURN MAX( _max, last_vs_today )

My other problem is also that i would like that if there's not and older incident than an incident, it doesn't make the difference between this incident and the start of my date table (I don't know if I'm clear here 😂 ).

It would be very cool if you could help me.

Tell me if you need more insight on my problem.

Thanks again.

BR,

Philippe.

[timalbers]

Hi @Anonymous 

regarding the start date issue, you could just add a filter on the _incidents VAR. I added another VAR after it, called _filtered_incidents. It filters out all records where there is no prev incident.

Here's what the updated code might look like:
 

Record de jours sans accidents =
VAR _incidents =
    ADDCOLUMNS(
        FILTER( 'Accidents du travail', 'Accidents du travail'[Type d'accident du travail] = "AT avec arrêt" ),
        "prev_incident",
        CALCULATE(
            MAX( 'Accidents du travail'[Date et heure.1]),
            FILTER(
                ALL( 'Accidents du travail' ),
                'Accidents du travail'[Date et heure.1] < EARLIER( 'Accidents du travail'[Date et heure.1] ) &&
                'Accidents du travail'[Type d'accident du travail] = "AT avec arrêt"
            )
        )
    )

VAR _filtered_incidents =
    FILTER(
        _incidents,
        NOT( ISBLANK( [prev_incident] ) )
    )

VAR _max =
    MAXX(
        _filtered_incidents,
        COUNTROWS(
            FILTER(
                'Calendar',
                'Calendar'[Date] > [prev_incident] &&
                'Calendar'[Date] <= [Date et heure.1] &&
                WEEKDAY( 'Calendar'[Date], 2 ) < 6
            )
        )
    )

VAR last_incident = MAXX( FILTER( 'Accidents du travail', 'Accidents du travail'[Type d'accident du travail] = "AT avec arrêt" ), 'Accidents du travail'[Date et heure.1] )

VAR last_vs_today =
    COUNTROWS(
        FILTER(
            'Calendar',
            'Calendar'[Date] > last_incident &&
            'Calendar'[Date] <= TODAY() &&
            WEEKDAY( 'Calendar'[Date], 2 ) < 6
        )
    )-1

RETURN MAX( _max, last_vs_today )

You also said, that something isn't working in your productive file? It's hard to tell.. Maybe you could elaborate a little what exactly the issue is? I will try my best to get this running...

[Anonymous]

Hello @timalbers  thanks again for your help.

I added your new variable and it work fine in the test file.

For the production file, I think my problem comes from the fact that I have incident from 4 different factory.

When i use the factory filter in my page, the incident record is not exact. But I think it's because theres no filter on the factory in the measure.

The column containing the name of the factory in my Accident table is named "Site".

Thanks for your help.

Br,

Philippe.

[Anonymous]

Thanks again @timalbers , it works like a charm.

Have a really nice day.

Br,

Philippe

[bhanu_gautam]

@Anonymous Create a calculated column for the previous incident date:

DAX
PreviousIncidentDate =
VAR CurrentIncidentDate = Incident[DateAndTime]
RETURN
CALCULATE(
MAX(Incident[DateAndTime]),
FILTER(
ALL(Incident),
Incident[DateAndTime] < CurrentIncidentDate
)
)

Create a measure to calculate the working days between each incident and its previous incident:

DAX
WorkingDaysBetweenIncidents =
VAR CurrentIncidentDate = MAX(Incident[DateAndTime])
VAR PreviousIncidentDate = MAX(Incident[PreviousIncidentDate])
RETURN
CALCULATE(
COUNTROWS(Dates),
FILTER(
Dates,
Dates[Date] > PreviousIncidentDate && Dates[Date] <= CurrentIncidentDate && WEEKDAY(Dates[Date], 2) < 6
)
)

Create a measure to find the maximum of these working days:

DAX
MaxWorkingDaysWithoutIncident =
MAXX(
ADDCOLUMNS(
Incident,
"WorkingDays", [WorkingDaysBetweenIncidents]
),
[WorkingDays]
)

[Anonymous]

Hi @bhanu_gautam , thanks for your answer, but when I try it, It doesn't seem to work.

Anyway, thanks for your help and have a nice day.