Forum Discussion

shyammayhs's avatar
shyammayhs
Advocate II
4 years ago

Loyal Vs Risk Customers

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!

Need Help

5 Replies

    • shyammayhs's avatar
      shyammayhs
      Advocate II
      4 years ago

      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's avatar
        PaulDBrown
        Community Champion
        4 years ago

        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.

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    4 years ago

    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!
  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    4 years ago

    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