I would like to show customer purchasing pattern from month to month.

[Jayprekk]

Actual Table:

User ID	Product	Month Year
100	A	Jan-22
100	A	Feb-22
100	B	Mar-22
100	A	Apr-22
101	B	Jan-22
101	B	Feb-22
101	C	Mar-22
102	A	Jan-22
102	C	Feb-22
102	B	Mar-22
102	C	Apr-22

I would like to show the product movement from month to month indicating the number of users:

	(No of users)Product A to A	(No of users)Product A to B	(No of users)Product A to C
From January 2022 to February 2022	2	0	1
From February 2022 to March 2022	0	1	0

Appreciate your support

[Jayprekk]

Thank you. Its working as I wanted it to :). Appreciate it!

[FreemanZ · Accepted solution]

hi @Jayprekk 

not sure if i fully get you, as the expected result seems inconsistent with the dataset.

Anyway, you may try the following:

1) get a date column in your table, like:

2) plot a table visual with the [Month Year] column and three measures like:

AtoA = 
VAR _list = 
CALCULATETABLE(
    VALUES(data[User ID]),
    data[Product]="A"
)
VAR _listpre =
CALCULATETABLE(
    VALUES(data[User ID]),
    ALL(data[Month Year]),
    data[Product]="A",
    data[Date]=EDATE(MAX(data[Date]), -1)
)
VAR result =
COUNTROWS(INTERSECT(_list, _listpre))+0
RETURN result

AtoB = 
VAR _list = 
CALCULATETABLE(
    VALUES(data[User ID]),
    data[Product]="B"
)
VAR _listpre =
CALCULATETABLE(
    VALUES(data[User ID]),
    ALL(data[Month Year]),
    data[Product]="A",
    data[Date]=EDATE(MAX(data[Date]), -1)
)
VAR result =
COUNTROWS(INTERSECT(_list, _listpre))+0
RETURN result

AtoC = 
VAR _list = 
CALCULATETABLE(
    VALUES(data[User ID]),
    data[Product]="C"
)
VAR _listpre =
CALCULATETABLE(
    VALUES(data[User ID]),
    ALL(data[Month Year]),
    data[Product]="A",
    data[Date]=EDATE(MAX(data[Date]), -1)
)
VAR result =
COUNTROWS(INTERSECT(_list, _listpre))+0
RETURN result

it worked like:

note: only user id 100 is using product A in Jan and Feb.