Lost Customer Sales

DAX DAX Calculations
1

Hello all, before posting this thread did review the resource(including Attribution Webinar) and some of the similar issues posted in the forum but no luck in resolving my issue, hence posting my question… Thanks in advance for reading my question and for your advise!

I am working on finding the lost customer (definition - sales in last 1 year - 90 days and no sales in the 90 days)
Using the resource i found here, i was able to get the count of lost customers displayed in a quarter over quarter context, however when i try to calculate the lost customer sales, i dont see any sales amount being generated…

Here’s the definition of Lost Customer Count - That’s working

Lost Customers =
Var BasePeriodCustomers = CALCULATETABLE(VALUES(FactSales[CustomerKey]),
FILTER(all(DimTime),
DimTime[Date]> min(DimTime[Date])-365 &&
DimTime[Date] < min(DimTime[Date])-ChurnParam[ChurnParam Value]))
Var CurrentPeriodCustomers = CALCULATETABLE(VALUES(FactSales[CustomerKey]),
FILTER(all(DimTime),
DimTime[Date]> min(DimTime[Date])-ChurnParam[ChurnParam Value] &&
DimTime[Date] < min(DimTime[Date])))
RETURN
COUNTROWS(EXCEPT(BasePeriodCustomers,CurrentPeriodCustomers))*-1

ChurnParam[ChurnParam Value] - is a parameter value, set to 90 days by default.

image

I am now trying to find those lost customer sales (highlighted customers sales)

Here’s how i have the Lost Customer Sales measure defined - and this is what is not returning any Sales Value…

Lost Customers($) =

VAR CustomersPurchased = CALCULATETABLE( VALUES( FactSales[CustomerKey] ),
FILTER( ALL( DimTime ),
DimTime[Date] > MIN( DimTime[Date] ) - 365 &&
DimTime[Date] < MIN( DimTime[Date] ) - 90 ) )
VAR PriorCustomers = CALCULATETABLE( VALUES( FactSales[CustomerKey] ),
FILTER( ALL( DimTime ),
DimTime[Date] > MIN( DimTime[Date] ) - 90 &&
DimTime[Date] < MIN( DimTime[Date] ) ) )
RETURN
CALCULATE(
CALCULATE( sum(FactSales[AmountAvgUSD]),
DATESBETWEEN( DimTime[Date], MIN( DimTime[Date] ) - 365, MIN( DimTime[Date] ) - 90 ) ) ,
EXCEPT( CustomersPurchased, PriorCustomers ) ) * -1

The above measure isn’t returning sales…

image

regards

2

Hi sanappi09,

Is it possible that you will attached the PBI file?

Have a good day!

Mariusz

3

Edna-CustomerAnalytics.pbix (2.4 MB)

hi Marius, i have attached the pbi file here… thanks again for your inputs!!!

4

Hi @sanappi09,

Thanks for the file.
I think, the following measure should do a trick:

Lost Customers( $ ) v3 =
VAR CustomersPurchased = CALCULATETABLE(
    VALUES( FactSales[CustomerKey] ),
        FILTER(
            ALL( DimTime ),
            DimTime[Date] > MIN( DimTime[Date] ) - 365 &&
            DimTime[Date] < MIN( DimTime[Date] ) - 90
        )
) 
VAR PriorCustomers = CALCULATETABLE(
    VALUES( FactSales[CustomerKey] ),
        FILTER(
            ALL( DimTime ),
            DimTime[Date] > MIN( DimTime[Date] ) - 90 &&
            DimTime[Date] < MIN( DimTime[Date] )
        )
) 

RETURN
CALCULATE(
    sum( FactSales[AmountAvgUSD] ),
    EXCEPT( CustomersPurchased, PriorCustomers )
)

File attached: Edna-CustomerAnalytics_v2.pbix (2.4 MB)

Take care.

Mariusz

5

that worked, thanks Marius…

1 Like
6

That’s great!