YOY Comparison in Line Chart measuring Orders from First to Fifth Velocity

[etane]

Hello.

Really stumped on this one.  I want to create line chart like this:

Lines: Customers who first purchased in 2024 for the 2024 line, and first purchased in 2025 for the 2025 line
X Axis: Order quantity 2 to 5

Y Axis: Average # of Days it takes customer to order X number of units

Working file linked here:  LINK

Thanks!

[etane]

@FBergamaschi problem is solved but sorry I short handed 2 - 5 to mean 1 to 2, 2 to 3... 4 to 5.

[v-tsaipranay · Accepted solution]

Hi @etane ,

Thank you for reaching out to the Microsoft fabric community forum. 

I’ve implemented the YOY velocity chart in your PBIX so you can review the full setup and DAX directly.
The logic is simple: each customer is assigned to a cohort year based on the date of their very first purchase. Then, their orders are ranked to calculate the number of days between consecutive purchases (GapDays).
A small Steps table (1st–2nd, 2nd–3rd, etc.) is used for the X-axis, and a measure computes the average GapDays for each step.
Finally, the line chart is built with StepLabel on the X-axis, the average days measure on the Y-axis, and CohortYear on the legend.

You can open the PBIX, verify the measures inside the A – Measures table, and adjust as needed.

Hope this helps. Please feel free to rech out for any further questions.

Thank you.

[etane]

@v-tsaipranay   Thank you for the PBIX.  Maybe I ask you to add one feature?  I'd like to be able to filter by Product to see it's order velocity.  I am not sure why this isn't working at the moment, but I can see OrderIndex is not affected by Product filter but Customer filter does work. 

For the screenshot example below, I hope to be able to see Order# 2050660 to be OrderIndex "1" instead of "2" when I have the following Customer and Product filter applied.  As result, the steplabel "1st - 2nd" does not appear in the line chart:

[v-tsaipranay]

Hi @etane ,

Thank you for the clarification and for providing the example screenshot. I understand now that when filtering by Product, the OrderIndex continues from the customer's overall order history rather than starting from their first purchase of that specific product. That's why the “1st–2nd” step is missing from your chart.

To resolve this, the OrderIndex calculation should take both Customer and Product into account. Updating it as suggested will reset the sequence for each customer/product pair.

OrderIndex = 
RANKX (
    FILTER (
        Orders,
        Orders[Customer_Number] = EARLIER ( Orders[Customer_Number] )
            && Orders[Product] = EARLIER ( Orders[Product] )
    ),
    Orders[Order Date],
    ,
    ASC
)

After this adjustment, filtering by Product will set the first order for that product as OrderIndex = 1, with subsequent orders following in order. This will allow the “1st–2nd” step and overall order velocity to display correctly at the product level.

Thank you.

[FBergamaschi]

I cannot understand this part of your post

X Axis: Order quantity 2 to 5

Y Axis: Average # of Days it takes customer to order X number of units

Can you explain better? In the chart X axis I see 1st - 2nd, 2nd - 3rd etc which is confusione

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI