Line chart like a running total, customer proportion of who had X days b/w first + last order

[Grygger]

I'm trying to recreate the visual chart below. The x axis represents the number of days, tracking the proportion of customer's who had X days between their first order and last order in the specified time range.

The linechart is the number of customers who have at least X amount of days between their first and last order, or more divided by the total # of customers with a first order in this time period.

i.e. By 100 days after the first order, roughly 85% of customers have had more orders, with their final order being at least 100 days after their first order in this time period.

My struggle is when using Customer IDs in a field, I'm able to correctly identifying the # of days between the first and last order for customers using this code 

Retention(Days) = DATEDIFF(CALCULATE(MIN(data_table[Purchase_Date])), CALCULATE(MAX(data_table[Purchase_Date])), DAY)

However, this visual below, I can't include the Customer ID into the fields and I'm struggling how to create something like a temporary table for these values that I could use to aggregate how many have met the criteria for X days between the two orders.

[v-yangliu-msft]

Hi  @Grygger ,

I created some data:

Here are the steps you can follow：

1. Create measure.

Measure =
var _mixorder=
MINX(FILTER(ALL('Table'),'Table'[Customer]=MAX('Table'[Customer])),[order])
var _maxorder=
MAXX(FILTER(ALL('Table'),'Table'[Customer]=MAX('Table'[Customer])),[order])
var _mindate=
CALCULATE(MAX('Table'[date]),FILTER(ALL('Table'),'Table'[order]=_mixorder))
var _maxdate=
CALCULATE(MAX('Table'[date]),FILTER(ALL('Table'),'Table'[order]=_maxorder))
return
DATEDIFF(_mindate,_maxdate,DAY)

2. Result:

If the result is not what you expected, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Grygger]

@v-yangliu-msft  Hi Liu,

So I've managed to get the first piece of the code down that calculates the # of days between the first and last order for each customer;

Retention(Days) = DATEDIFF(CALCULATE(MIN(data_table[OrderDate])),
                            CALCULATE(MAX(data_table[OrderDate])),
                            DAY)

The second piece that I'm still struggling on is the chart visual itself. The logic behind it basically says from 0 to X days from now; what's the count of customers who have been retained (based on the measure) for at least X days or more!

i.e. on the x axis of that chart, if it was day 40, how many customers have their retention measure return as 40 or greater. That count is then divided by all overall customers in the specified time range.