Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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 table considering that each row represent one repair for one car number. Now I want to calculate the average time (day) between two repair for each car number.
How can I implement this in power BI? With DAX or M?
Any comment is appreciated.
You need to update your version of Power BI desktop. ROWNUMBER is fairly new but OFFSET was in the December release.
You 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 )
)
)
it seems that it does not recognize the functions
User | Count |
---|---|
139 | |
70 | |
67 | |
52 | |
52 |
User | Count |
---|---|
210 | |
92 | |
64 | |
59 | |
56 |