Active Customers by day

[Anonymous]

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_date	customer_id	product	price
1/1/2021	1	product a	100.00

customer table

customer_id	customer name	Last order date	days since
1	Joe	1/1/2021	today()-last order date

[v-janeyg-msft · Accepted solution]

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.

[v-janeyg-msft]

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]

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]

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

[Anonymous]

Yeah that picture was from your file. Zoom to today and there should only be one active

[Anonymous]

[Singaravelu_R]

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]

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

[v-janeyg-msft]

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

[Anonymous]

Here is my current setup in PBI file and then I made a chart for what I am looking for. Active is if you placed an order in the last 100 days but I want to be able to see it historically instead of how I have it currently which is just off of today()

PBI File 

[v-janeyg-msft]

Hi,  @Anonymous 

I'm sorry that monday is too busy, no time to reply. I see that your problem changes every time, it is difficult for me to understand what you are asking for.

I try to understand the requirements you described last time: Do you need to distinguish between active customers and inactive customers based on month and whether the date of the customer's last order is more than 100 days? Then I want to ask, there are different years in your date, how do you distinguish it? And if the user places an order in the current month, but the last order date has changed, shouldn’t it be counted in the current month?

Calculation is not difficult, but a complete logic is necessary before I can calculate. Can you explain more clearly?

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]

I would just want the formula using my datekey so I could pick and date in history and it do a 100 look back at active customers. For example if I put in 3/1/2021 it would just look at 3/1/2021 and minus 100 days to see who was active. It would ignore customer orders 3/2/2021 to today. 

[v-janeyg-msft]

Hi,  @Anonymous  

According to your description, You want to choose a date. If a customer has an order within 100 days of this date, then he is an active user, right?

But there is only month in this table, it will generate the question I replied last time, so what is the final result you want? How to achieve it is not difficult, as long as you are sure what you want.

Best Regards

Janey Guo

[Anonymous]

instead of month think of it as 1/31/2021, 2/28/2021, etc 

[TomMartens]

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]

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