Calculate active customers whose last sale was in last 12 months

[Anonymous]

Trying to work out number of active customers checking if their lapsed date has been reached. Lapsed date is when there is no order for customer in last 12 months

# Active Cust = 
VAR dt =
    CALCULATE (
        MAX ( 'Calendar'[Date] ),
        ALLSELECTED ( 'Calendar'[Date] )
    )

VAR ActiveCustomers =
    ADDCOLUMNS (                            
        VALUES ( Sales[Sell-to Customer No_] )
        ,"@LapsedDate", [CustomerLapsedDate] ) -- measure lasped date , last order date + 12 months

var __activeCusts =
COUNTROWS (
    DISTINCT (
            FILTER ( ActiveCustomers, [@LapsedDate] > dt)
        )
)
return  __activeCusts

Desired Result with correct active customers, the total is actually correct as it stand 33 customers have not lasped. However I want this view at month level. So June should show 33

 

[Anonymous · Accepted solution]

Solved it in the end, was simply this!

# Active Customers = 
VAR EndDate = MAX('Calendar'[Date])
VAR StartDate =EDATE(EndDate,-12)
VAR Result = 
CALCULATE(
[# Customers],DATESBETWEEN(Calendar[date],StartDate,EndDate)
)

RETURN
    IF (Result = BLANK(), 0, Result)

[Anonymous]

Can i include anything extra to help arrive at a solution, new to psoting on the forum thanks?