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
