Forum Discussion
Calculating maximum time between two records
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 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]
)
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.