Helping creating measure that counts customers dynamically

[Anonymous]

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.

[Anonymous]

Thank you so much for your help. It worked 🙂 

[Anonymous]

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

[JarroVGIT · 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.:

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