Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I need some help with creating a measure to do the following.
I have the following table with unique customers and unique dates:
Customer | Last Customer Transaction |
A | 1-Dec-19 |
B | 1-Dec-19 |
C | 1-Dec-19 |
D | 1-Dec-19 |
E | 1-Nov-19 |
F | 1-Oct-19 |
G | 1-Oct-19 |
H | 1-Oct-19 |
I | 1-Sep-19 |
J | 1-Sep-19 |
K | 1-Jul-19 |
L | 1-Jun-19 |
M | 1-Jun-19 |
N | 1-Jan-19 |
O | 1-Dec-18 |
P | 1-Dec-18 |
Q | 1-Nov-18 |
R | 1-Oct-18 |
S | 1-Sep-18 |
T | 1-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.
Solved! Go to 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.:
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! 🙂
Proud to be a Super User!
Thank you so much for your help. It worked 🙂
I forgot to mentioned that I would like to have as a final result the following table:
December 2019 | November 2019 | October 2019 | September 2019 | |
Count | 16 | 13 | 13 | 11 |
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.:
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! 🙂
Proud to be a Super User!
User | Count |
---|---|
47 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |