topic I would like to show customer purchasing pattern from month to month. in DAX Commands and Tips

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

Jayprekk — Sun, 21 May 2023 21:57:11 GMT

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

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

FreemanZ — Mon, 22 May 2023 07:30:11 GMT

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.

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

Jayprekk — Mon, 22 May 2023 21:37:42 GMT

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