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 )
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.
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 _maxI 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
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 )