topic Helping creating measure that counts customers dynamically in DAX Commands and Tips

Helping creating measure that counts customers dynamically

Anonymous — Fri, 27 Dec 2019 16:51:18 GMT

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.

Re: Helping creating measure that counts customers dynamically

Anonymous — Fri, 27 Dec 2019 17:00:33 GMT

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

Re: Helping creating measure that counts customers dynamically

JarroVGIT — Fri, 27 Dec 2019 21:39:21 GMT

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! 🙂

Re: Helping creating measure that counts customers dynamically

Anonymous — Fri, 03 Jan 2020 16:41:30 GMT

Thank you so much for your help. It worked 🙂