Forum Discussion

ryanjparks's avatar
ryanjparks
Icon for Helper I rankHelper I
3 years ago
Solved

Active Customer Count - Last 12 Months, Per Month

Hi everyone,

 

I'm thinking this should be pretty easy, but I'm having a hard time figuring out where I'm going wrong.

 

I am attempting to get an active customer count on a monthly basis (Active means any Customer who has purchased something in the last 12 months)

 

I need it to be like the following:

 

January 2023 = Distinct Count of Customers who bought between 2/1/22 - 1/31/23

February 2023 = Distinct Count of Customers who bought between 3/1/22 - 2/28/23

March 2023 = Distinct Count of Customers who bought between 4/1/22 - 3/31/23

And so on....

 

I have a Sales table with the Customer ID and the Sales Date. Additionally, I have a Calendar Table with just a date for each day.

 

I currently have a measure that has the following:

 

# Active Customers Rolling 12 Mo =
    CALCULATE(
        DISTINCTCOUNTNOBLANK(Sales[CustomerID]),
        DATESBETWEEN(Calendar[Date],EOMONTH(TODAY(),-12),EOMONTH(TODAY(),-1)
        )
    )
 
I've placed this measure in a Matrix that has a Bin of Months on Sales[InvoiceDate] for the Columns (each column is a different month). The measure was placed in the Values box. The result is giving me a unique count of customers who have purchased for that given month, but the amount but is not adding up the total for the annual distinct count for the 12 month period I need.
 
Where am I going wrong?
 
Thanks for any help...

 

  • ryanjparks's avatar
    ryanjparks
    3 years ago

    I was able to figure it out. This was the measure that got it to work:

     

    # Active Customers FILTERED =
    var MaxDate = MAX( Calendar[Date] )
    return
        CALCULATE(
            DISTINCTCOUNTNOBLANK( Sales[CustomerID] ),
            FILTER(
                ALL ( Calendar ),
                AND( Calendar[Date] <= MaxDate, Calendar[Date] >= MaxDate - 365)
            )
        )

2 Replies

  • ryanjparks's avatar
    ryanjparks
    Icon for Helper I rankHelper I
    3 years ago

    I was able to figure it out. This was the measure that got it to work:

     

    # Active Customers FILTERED =
    var MaxDate = MAX( Calendar[Date] )
    return
        CALCULATE(
            DISTINCTCOUNTNOBLANK( Sales[CustomerID] ),
            FILTER(
                ALL ( Calendar ),
                AND( Calendar[Date] <= MaxDate, Calendar[Date] >= MaxDate - 365)
            )
        )