Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Calculating maximum time between two records

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.

Need Help
  • timalbers's avatar
    timalbers
    1 year ago

    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 )

     

9 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

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

      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.

  • timalbers's avatar
    timalbers
    Skilled Sharer
    1 year ago

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

      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's avatar
        timalbers
        Skilled Sharer
        1 year ago

        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 )