How to determine first order for every customer?

SimLoe · ‎10-21-2019

Hi all,

I have a table containing orders from our customers and would like to classify the type of the order: ("FirstPurchase", "Extension", "Upgrade"). For your understanding: The table contains orders of a software that and is licensed per managed asset. With every purchase, the customer is entitled to all upgrades that are being released while the Software Assurcance (ProductId= SWA) is active. The Software Assurcance is always included for one year and can then be extended.

For example, one customer could have ordered the following:

Date CustomerId ProductId PlanId Quantity Expires

01/20/2015 12345 ABCDE lifetime 200 null

01/20/2015 12345 SWA null 200 01/20/2016

06/20/2015 12345 ABCDE lifetime 50

06/20/2015 12345 SWA null 50 06/20/2016

11/01/2015 12345 FGHIJ lifetime 250 null

We may have released a new version on 10/31/2015, so the customer was entitled to a (free) upgrade for a total of 250 assets.

I would like to introdruce a new calculated column "OrderType" that classifies these orders for every customer. In the first example it should look like the following:

Date CustomerId ProductId PlanId Quantity Expires OrderType

01/20/2015 12345 ABCDE lifetime 200 null FirstPurchase

01/20/2015 12345 SWA null 200 01/20/2016 null

06/20/2015 12345 ABCDE lifetime 50 Extension

06/20/2015 12345 SWA null 50 06/20/2016 null

01/11/2015 12345 FGHIJ lifetime 250 null Upgrade

To make things more complicated, there are also some other cases, where a customer had first a (free) trial license before deciding to order:

Date CustomerId ProductId PlanId Quantity Expires OrderType

01/20/2017 78910 QWERT trial 200 02/20/2017 null

02/20/2017 78910 QWERT lifetime 75 null FirstPurchase

02/20/2017 78910 SWA null 75 02/20/2018 null

11/01/2018 78910 RZKQL lifetime 75 null Upgrade

And there is also one last special case, where the "FirstPurchase" may consist of several orders on the same date (because the customer wanted to have split licenses for several sites).

Date CustomerId ProductId PlanId Quantity Expires OrderType

01/20/2019 96385 QWERT trial 1000 null null

02/20/2019 96385 QWERT subscription 200 02/20/2020 FirstPurchase

02/20/2019 96385 SWA null 1000 02/20/2020 null

TL;DR: I need to introdruce a new calculated column that classifies the type of purchase.

In order to find out if an order was a "FirstPurchase", I think the right approach would be to filter the long list of orders for every distinct CustomerId, look for the earliest order that has a PlanId of "lifetime" or "subscription". In order to pay attention to the last mentionned special case, it would be necessary to group those orders by date and mark all orders of the earliest date as "FirstPurchase".

In order to determine "Upgrades", I need to check whether there were already some purchases for previous versions.

Unfortunately, I do not know how to approach this issue in Power BI / DAX, so I would appreciate any feedback.

Cheers,

Simon

v-xicai · ‎10-21-2019

Hi @SimLoe ,

You may create column like DAX below.

OrderType=
var _FirstDate= CALCULATE(MIN(Table1[Date]),FILTER(ALLSELECTED(Table1),Table1[CustomerId]=EARLIER(Table1[CustomerId])&&Table1[PlanId]=EARLIER(Table1[PlanId])))
Return
IF(Table1[Date]= _FirstDate, "FirstPurchase", BLANK())

If I misunderstood it, could you please share your sample data and desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

Best Regards,

Amy

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

SimLoe · ‎10-22-2019

Hi @v-xicai,

thank you very much for your help, this was exactly what I was looking for with respect for the "FirstPurchase".

I just added some filters and have I have to use ";" in German so the DAX looks like this know:

OrderType = 
var _FirstDate= CALCULATE(MIN(Orders[Issued]);FILTER(ALLSELECTED(Orders);Orders[CustomerId]=EARLIER(Orders[CustomerId])&&Orders[PlanId]=EARLIER(Orders[PlanId])))
Return
IF(Orders[Issued]= _FirstDate && Orders[ProductId]="11111111-1111-0000-0000-000000000000" && Orders[PlanId]<>"test" && Orders[PlanId]<>"temp"; "FirstPurchase"; BLANK())

However, can you help me to understand the definition of the variable "_FirstDate" in your code so I will hopefully be able to create the other script for assigning the other possible options for "OrderType".

Within the CALCULATE function, you are finding the minimum date for all orders that apply to the following filter conditions:

FILTER(ALLSELECTED(Orders);Orders[CustomerId]=EARLIER(Orders[CustomerId])&&Orders[PlanId]=EARLIER(Orders[PlanId])

Can you help me translate this to pseude code? I am filtering for all orders for the first customer I can find and then continue with the next customer or something like this?