Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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.

DAX
DAX Calculation
Measures
  • JarroVGIT's avatar
    JarroVGIT
    6 years ago

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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

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

     

     December 2019November 2019October 2019September 2019
    Count16131311

     

     

    • JarroVGIT's avatar
      JarroVGIT
      Icon for Resident Rockstar rankResident Rockstar
      6 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Thank you so much for your help. It worked 🙂