Loyal Vs Risk Customers

[shyammayhs]

Hi guys,

I would like to to see the DISTINCTCOUNT of Customers that have purchased in the last 3-12 months and also that havent purchased in the last 2months. I have thought about Virtual tables/CALCULATEDTABLE etc, but nothing is working for me at the moment!

Has anyone come across a problem like this before?

Thanks guys!

[v-luwang-msft]

Hi @shyammayhs ，

Could you pls share your  data model.You could attach your pbix on a public link.And remember remove confidential data.

Best Regards

Lucien

[AllisonKennedy]

@shyammayhs 

Here is a measure I have used for this: 

Inactive Customer Count =
VAR _InactiveDate = MIN('Inactive Date Range'[Date])
COUNTROWS(FILTER('DimCustomer', [Sales]>0 && 'DimCustomer'[Last Transaction Date]< _InactiveDate ))

Note it uses an unrelated table for _InactiveDate so that you can adjust how you define 'active', rather than hard coding it into the measure (these are some of the questions @PaulDBrown is asking you to answer).  That Inactive Date Range table just needs a Date column, or anything else you'd want to use in the filters: 

In the screenshot above, the date filter on left is my DimDate table, and on the right is the Inactive Date Range table.

I have a visual level filter on the Inactive Date Range filter using this measure, so that you can't pick a date before the DimDate selection: 

Inactive Date Filter = COUNTROWS(FILTER('Inactive Date Range','Inactive Date Range'[Date] >= MIN('Calendar'[Date]) ))

Hope that helps!

[PaulDBrown]

Please provide sample data or PBIX file. Thanks!

[shyammayhs]

Hi @PaulDBrown, The data I am using is sensitive. But I will try to come up with a mock up. I am assuming you haven't encountered a scenario like this before!

Thanks

[PaulDBrown]

It's a variation of the "new (and/or lost) customers" calculation. Yet it's much more productive to work on an actual model than to imagine tables/relationships/measures. So a mock up would be really helpful.

Also please clarify what "in the last 3-12 months" means (customers who made purchases within Today - 3 months to Today-12 months?

A depiction of how you wish to present these calculations is also needed.