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

Anonymous
Not applicable

## Customer Stickiness - Last Purchase analysis

I wish to create a matrix that shows the number of customers that made purchases in a month while segmenting it against the last purchase date.

 April May June July August Sept 0-30 days 700 520 31-60 days 200 180 61-90 days 100 140 91-120 days 120 160 121-150 days 70 50 151-180 days 50 20 180 + days 10 40 Total customers 1250 1110

I have tried the below codes, but I am not able to create the exact table.

Order Days difference =
VAR Secondlastorder =
CALCULATE (
MAX ( 'Sales Data'[Sales Order Date] ),
FILTER ( 'Sales Data', 'Sales Data'[Sales Order Date] < MAX ( 'Sales Data'[Sales Order Date] ) )
)
VAR LastOrder =  LASTDATE('Sales Data'[Sales Order Date])

Return DATEDIFF (Secondlastorder,LastOrder,DAY)

Order date group = IF([Order Days difference]<=30,"0-30 days",IF([Order Days difference]<=60,"31-60 days",IF([Order Days difference]<=90,"61-90 days",IF([Order Days difference]<=120,"90-120 days",IF([Order Days difference]<=150,"120-150 days","150+")))))

I am getting order date groups as values which I want to be shown as row categories.

Further, I also wish to create the below table to analyze the order size and frequency by which the customer places an order:
 Q1 Q2 Number of Orders number of customers Average Order value number of customers Average Order value 1 700 10000 2 800 12000 3 200 14000 4 400 11000 5 500 20000 5+ 1000 80000 Total customers 3600 147000

Kindly guide
2 REPLIES 2
Community Support

Hi @Anonymous ,

Please share some sample data so that we could test the formula.

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Anonymous
Not applicable

please find demo table as requested

 Billing Doc No Billing Date Sales Order Sales Order Date Customer Name 29 12.04.2022 21 16.03.2022 am 30 07.05.2022 22 21.03.2022 am 31 16.04.2022 22 21.03.2022 am 31 16.04.2022 22 21.04.2022 am 32 14.04.2022 23 30.03.2022 ch 33 05.04.2022 24 31.03.2022 ch 33 05.04.2022 24 31.03.2022 ch 33 05.04.2022 24 31.03.2022 ar 34 12.04.2022 24 31.03.2022 ar 34 12.04.2022 25 31.04.2022 ar 34 12.04.2022 25 31.04.2022 ar 34 12.04.2022 25 31.04.2022 ar 34 12.04.2022 25 31.04.2022 ar 34 12.04.2022 25 31.04.2022 ar 34 12.04.2022 26 31.05.2022 ar 34 12.04.2022 26 31.05.2022 ar 34 12.04.2022 26 31.05.2022 ar 34 12.04.2022 26 31.05.2022 ar

## Helpful resources

Announcements

#### 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.

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors