Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Active Customers by day

I currently count active and unactive customers based on customers who have been in +/- 100 days. My measures only give me a current day view of the active customers. Would it be possible to get a measure of active and unactive by day? Here is my set up:

date table

datekey

1/1/2021
1/2/2021

 

order table

order_datecustomer_idproductprice 
1/1/20211product a100.00 

 

customer table

customer_idcustomer nameLast order datedays since
1Joe1/1/2021today()-last order date
Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    5 years ago

    Hi, Anonymous 

     

    I'm sorry. I modify the measure.

    active = 
COUNTX (
    FILTER (
        ALL ( Customers ),
        Customers[CURRENT last order]
            >= SELECTEDVALUE ( 'Table'[Date] ) - 100
            && [CURRENT last order] <= SELECTEDVALUE ( 'Table'[Date] )
    ),
    [customerID]
) + 0
    inactive = 
COUNTX (
    FILTER (
        ALL ( Customers ),
        Customers[CURRENT last order]
            < SELECTEDVALUE ( 'Table'[Date] ) - 100
            || [CURRENT last order] > SELECTEDVALUE ( 'Table'[Date] )
    ),
    [customerID]
) + 0

    Best Regards

    Janey Guo

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

16 Replies

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    5 years ago

    Hey Anonymous ,

     

    please describe your requirements in more detail and provide sample data that reflects your data model. Use the sample data to explain the expected result.

     

    Regards,

    Tom

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      The main thing I want is to make a table with all dates and a measure to show the active and unactive members at that time period. In this example I would say only have 200 total customers in this time period but every day 1 unactive customer came in so it moved them. Let me know if this helps explain better

  • Singaravelu_R's avatar
    Singaravelu_R
    Icon for Resolver III rankResolver III
    5 years ago

    Add the calculated column in the customer table

    Active or In active customer = if (DATEDIFF(today(),last order date())=100,  "Active Customer","Inactive Customer")

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Don't think this would give me historical view of how many active and unactiave I have

      • v-janeyg-msft's avatar
        v-janeyg-msft
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi,  Anonymous 

         

        Can you explain the logic of active and inactive? Is it inactive if you don’t place an order on the same day?

        Secondly, Can you share some more sample fake data? So we can quickly help you deal with the problem.

         

        Best Regards

        Janey Guo

         

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, Anonymous 

     

    According to your last reply, You need to create a single date list as slicer and create two measure to calculate active and inactive.

    Like this:

    Table = DISTINCT('Date'[Date])
    active = COUNTX(FILTER(ALL(Customers),DATEDIFF(SELECTEDVALUE('Table'[Date]),[CURRENT last order],DAY)<=100),[customerID])+0
    inactive = COUNTX(FILTER(ALL(Customers),DATEDIFF(SELECTEDVALUE('Table'[Date]),[CURRENT last order],DAY)>100),[customerID])+0

     

    If it doesn’t solve your problem, please feel free to ask me.

     

    Best Regards

    Janey Guo

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Doesn't look like that will work. If you zoom forward to today, there should be 1 active customer (made order on 3/1/21) and 4 inactive. 

       

      • v-janeyg-msft's avatar
        v-janeyg-msft
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi, Anonymous 

         

        Did you download my file? Have you used new date column as slicer and use it in table instead of old?

         

        Best Regards

        Janey Guo

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, Anonymous 

     

    I'm sorry. I modify the measure.

    active = 
COUNTX (
    FILTER (
        ALL ( Customers ),
        Customers[CURRENT last order]
            >= SELECTEDVALUE ( 'Table'[Date] ) - 100
            && [CURRENT last order] <= SELECTEDVALUE ( 'Table'[Date] )
    ),
    [customerID]
) + 0
    inactive = 
COUNTX (
    FILTER (
        ALL ( Customers ),
        Customers[CURRENT last order]
            < SELECTEDVALUE ( 'Table'[Date] ) - 100
            || [CURRENT last order] > SELECTEDVALUE ( 'Table'[Date] )
    ),
    [customerID]
) + 0

    Best Regards

    Janey Guo

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.