Active Products Path for each customer

Hey all,

I need some help with an “ActiveProductPath”, hopefully someone can help me.

I want for each customer the path of the active products, see attachment,

https://www.dropbox.com/s/1jfqupyrfdwks3t/Dummy-file%20ActiveProductPath.xlsx?dl=0

For example customer 1 has bought 4 products in 2019.

On 01-04-2019 was only product A active, on 01-07-2019 also product A, on 01-10-2019 the products B and C were active and on 01-01-2020 is product D active.

The endresult on 01-04-2019 should be:

Customer ActiveProductPath
1 A
2 E
3 H,I
4

Furthermore, the “path” for each customer contains unique products, so if a customer has bought multiple items of a product (for example 4 times product A), the endresult gives only one A.

Can someone help me?

Thanks in advance!

Greetings from The Netherlands,

Cor

Hi Cor,

Hope you are well.

Please find attached a PBIX file with your scenario in question.

I added a simple date table (Availaible on Enterprise) this is a must for any type of analysis over time, it makes life much easier and calculations easier!!! I have just placed quarter into the matrix but you could easily place anything from the date table there or create a new column.

I created a calculated column that gives the status of the product. So you can filter to see if the product was active or not based on the invoice. Going forward depending on the requirement you could even create a product dimension (lookup table).

I then created 2 simple measures, one to return the total products sold (5) and the second returns the product value (i.e A, B).

Hope this is what you’re after, any questions shout.

H

Active Products Path for each customer-CorvanD.pbix (118.5 KB)

1 Like

Hi H,

Thanks for your reply!
Your answer isn’t exactly what I want, I think I didn’t make clear what I want in my question.
You’ve picked the invoicedate and the invoicedate isn’t important, it’s about the From-date and the To-date. The solution is in the “events in progress”-technique. I’ve used this technique in the file in the attachment. ActiveProductPath.pbix (89.2 KB)

Part of the challenge is solved, now the last part.

For every customer I want for each ref Ref_Date’[FullDate] the ActiveProductPath in a table in Power Query so I can use a slicer and count the customers who had a certain ActiveProductPath.

In the end I want an answer for the question: “how many customers have ActiveProductPath “x” on a certain day?”

Can you help me with the last part of the solution?

Thanks in advance,

Cor

1 Like

I’ve made another dummy file, hopefully you will understand what I mean.

ActiveProductPathv2.pbix (97.4 KB) Dummy-file ActiveProductPathv2.xlsx (146.2 KB)

I want to count each ActiveProductsPath for each date.

The result should be:

Date A B C A, B
1-4-2019 2 1
1-7-2019 1 1 1
1-10-2019 2
1-1-2020 1 2

Can someone help me?

Hi Cor,

So for any given quarter you want to know the products which were active between that period.

Looking at your PBIX file you already have the measure #No of Active Products placing this in the matrix visual with product in the columns and quarter in the rows gives the result you are after.

Please explain further if this isn’t the case.

Regards,
H

Hi H,

All I want is to count for every day how many customers have “ActiveProductPath” A, or B or the combination of A,B or A,B,C etc… I have for every customer the path for each day (workbook 2) but I want to count for every day the total number of customers who has a certain “ActiveProductPath”.
I think we need in data view a extra calculated column that calculates the active path. With this calculated column we can put a slicer into the report view and put a SUM-measure to it.

Regards,

Cor

Hi all,

I have found a solution for this challenge, you can find the solution here: ActiveProductPathFinalv2.pbix (118.8 KB)

This solution isn’t working for large datasets so I will make another question for the community.

Regards,

Cor