Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
ryanjparks
Frequent Visitor

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...

 

1 ACCEPTED SOLUTION
ryanjparks
Frequent Visitor

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)
        )
    )

View solution in original post

2 REPLIES 2
ryanjparks
Frequent Visitor

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)
        )
    )
ryanjparks
Frequent Visitor

Any ideas, anyone?

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

Top Kudoed Authors