Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
alisoroush77777
New 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 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.

3 REPLIES 3
johnt75
Super User
Super User

You need to update your version of Power BI desktop. ROWNUMBER is fairly new but OFFSET was in the December release.

johnt75
Super User
Super User

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

Annotation 2023-05-22 150337.jpg

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors