AVG buying frequency < last time sales

[Anonymous]

Hi Guys, 

I want to see over months (historical) how many customers I have that have no sales for a longer period than their AVG buying frequency in days. I have the following data in my sales table: 

Now I have two questions;

How can I calculate the AVG buying frequency and how can I calculate the last time sales. 

Based on these two measures I need to create an thirth measure where I want to count the number of customers where the last time sales is bigger than the avg buying frequency in days. 

As a result of these measures I want to create an chart where i can see the "unactive" customers per month / week. 

Hope someone can help!

[Ashish_Mathur]

Hi,

In a simple table, please show the exact result that you are expecting with an explanation.

[Icey]

Hi @Anonymous ,

Is this problem solved?

Best Regards,

Icey

[Icey · Accepted solution]

Hi @Anonymous ,

Please check if this is what you want:

1. Create a Calendar table.

Calendar = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2020, 2, 29 ) ),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "Week", WEEKNUM ( [Date] )
)

2. Create relationship.

3. Create measures.

AVG buying frequency = DIVIDE ( COUNT ( Sales[Customer] ), COUNT ( 'Calendar'[Date] ) )

last time sales =
VAR LastDate_ =
    MAX ( Sales[Date] )
RETURN
    CALCULATE ( SUM ( Sales[Sales amount] ), Sales[Date] = LastDate_ )

Avg Sales =
AVERAGEX (
    SUMMARIZE (
        'Calendar',
        'Calendar'[Year],
        'Calendar'[Month],
        'Calendar'[Week],
        "SalesSum", CALCULATE ( AVERAGE ( Sales[Sales amount] ), ALLSELECTED ( Sales ) )
    ),
    [SalesSum]
)

Count of active customers = 
CALCULATE (
    DISTINCTCOUNT ( Sales[Customer] ) + 0,
    FILTER ( Sales, [last time sales] > [Avg Sales] )
)

Unactive = IF([last time sales]<[Avg Sales],1)

4. Create visulas.

For more details, please check the attached PBIX file.

Best Regards,

Icey

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

[amitchandak]

@Anonymous This column will give you last purchase date

maxx(filter(table,table[customer] =earlier(table[customer]) && table[date] <earlier(table[date])),table[date])

This measure will give you avg buying frequency of customer

averagex(summarize(table,table[customer], "_cnt", count(table[date])),[_cnt])

[Anonymous]

First measure doesn't work andt second measure don't give me the right outcome 😞 

At the second measure i get at customers that buy a lot and often a really high outcome, which should be low.