Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Jayprekk
Frequent Visitor

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

Actual Table:

User IDProductMonth Year
100AJan-22
100AFeb-22
100BMar-22
100AApr-22
101BJan-22
101BFeb-22
101CMar-22
102AJan-22
102CFeb-22
102BMar-22
102C

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 2022201
From February 2022 to March 2022010

 

Appreciate your support

1 ACCEPTED SOLUTION
FreemanZ
Super User
Super User

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:

FreemanZ_0-1684739815235.png

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:

FreemanZ_2-1684740566591.png

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

View solution in original post

2 REPLIES 2
Jayprekk
Frequent Visitor

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

FreemanZ
Super User
Super User

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:

FreemanZ_0-1684739815235.png

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:

FreemanZ_2-1684740566591.png

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors