Show migration between sales channels

[mno3nrg]

Hello, I'm new to DAX and I'm unfortunately not getting anywhere at this point. I have a table with sales data. Each sale is assigned to a sales channel. I want to use a Sankey chart to show how customers churn between sales channels when they order multiple times.

My thought was: I use the sales channel column given so far and add another column which contains the sales channel of the next order, if there is another order. So it should show the next sales channel if the customer number stays the same and the column "Order / Customer" grows by 1. Unfortunately I have no idea how to formulate this in DAX.

In Sankey I would use the actual sales channel as the start and the new column as the target. Does this make sense, and what would happen if customers order only once and then the new column remains empty?

Here is my current table with orders:

order_id	customer_id	sales channel	order / customer	following sales channel
1	11	Amazon	1	ebay
2	11	ebay	2	-
3	12	Amazon	1	-
4	13	Shop	1	Amazon
6	13	Amazon	2	Amazon
7	13	Amazon	3	-

If my approach does not make sense, please feel free to give me a better suggestion. It doesn't matter how, but I would like to be able to show the churn between distribution channels.

Thank you very much!

[amitchandak · Accepted solution]

@mno3nrg , In case you need following sales channel, a new column

following sales channel =
var _max = minx(filter(Table, [customer_id] =earlier([customer_id]) && [order_id] > earlier([order_id])),[order_id])
return
maxx(filter(Table, [customer_id] =earlier([customer_id]) && [order_id] =_max ),[sales channel])