Forum Discussion
alisoroush77777
3 years agoNew Member
find the difference date between two unique car number
hi I have a table that has 2 columns in it. one is car number and another is repair date. I have 5 car that is repaired regularly and each time the car number and repair date is addet into this tabl...
johnt75
Super User
3 years agoYou will need a column which uniquely identifies each row. If you don't have one already you can use Power Query to add an index column. In the modelling view you need to select the table and mark the unique column as the key column for the table.
Then you could create a measure like
Avg days between repair =
AVERAGEX (
'Table',
VAR CurrentRow =
ROWNUMBER (
ALL ( 'Table' ),
ORDERBY ( 'Table'[Repair date], ASC ),
PARTITIONBY ( 'Table'[Car number] )
)
RETURN
IF (
CurrentRow > 1,
VAR CurrentDate = 'Table'[Repair date]
VAR PrevDate =
SELECTCOLUMNS (
OFFSET (
-1,
ALL ( 'Table' ),
ORDERBY ( 'Table'[Repair date], ASC ),
PARTITIONBY ( 'Table'[Car number] )
),
"@repair date", 'Table'[Repair date]
)
RETURN
DATEDIFF ( PrevDate, CurrentDate, DAY )
)
)
alisoroush77777
3 years agoNew Member
it seems that it does not recognize the functions