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
Anonymous
Not applicable

Helping creating measure that counts customers dynamically

Hello,

 

I need some help with creating a measure to do the following.

 

I have the following table with unique customers and unique dates:

 

CustomerLast Customer Transaction
A1-Dec-19
B1-Dec-19
C1-Dec-19
D1-Dec-19
E1-Nov-19
F1-Oct-19
G1-Oct-19
H1-Oct-19
I1-Sep-19
J1-Sep-19
K1-Jul-19
L1-Jun-19
M1-Jun-19
N1-Jan-19
O1-Dec-18
P1-Dec-18
Q1-Nov-18
R1-Oct-18
S1-Sep-18
T1-Aug-18

 

I would like to count the number of customers in which its last transaction was within one year:

For instance:

If we consider December 2019 = 16 customers (From A to P)

If we consider November 2019 = 13 customers  (From E to Q)

If we consider October 2019 = 13 customers  (From F to R)

If we consider Septmber2019 = 11 customers (From I to S)

 

Thank you so much and any help would be much appreciated.

1 ACCEPTED SOLUTION

I have imported your data as Table1 and created the following measure:

Measure = 
IF(HASONEVALUE(Table1[Last Customer Transaction]), 
    COUNTROWS(
        FILTER(ALL(Table1), 
        Table1[Last Customer Transaction] >= SELECTEDVALUE(Table1[Last Customer Transaction])-365 
        && Table1[Last Customer Transaction] <= SELECTEDVALUE(Table1[Last Customer Transaction]))
    ), BLANK()
)

This resulted in the following Matrix visual, when filtered on the top 4 [Last Customer Transaction] using the FIlter pane.:

image.png

Let me know if this works for you! 🙂

 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Thank you so much for your help. It worked 🙂 

 

Anonymous
Not applicable

I forgot to mentioned that I would like to have as a final result the following table:

 

 December 2019November 2019October 2019September 2019
Count16131311

 

 

I have imported your data as Table1 and created the following measure:

Measure = 
IF(HASONEVALUE(Table1[Last Customer Transaction]), 
    COUNTROWS(
        FILTER(ALL(Table1), 
        Table1[Last Customer Transaction] >= SELECTEDVALUE(Table1[Last Customer Transaction])-365 
        && Table1[Last Customer Transaction] <= SELECTEDVALUE(Table1[Last Customer Transaction]))
    ), BLANK()
)

This resulted in the following Matrix visual, when filtered on the top 4 [Last Customer Transaction] using the FIlter pane.:

image.png

Let me know if this works for you! 🙂

 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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