topic Re: Calculate active customers whose last sale was in last 12 months in DAX Commands and Tips

Calculate active customers whose last sale was in last 12 months

Anonymous — Wed, 08 Jun 2022 08:35:17 GMT

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

Re: Calculate active customers whose last sale was in last 12 months

Anonymous — Wed, 08 Jun 2022 14:30:44 GMT

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

Re: Calculate active customers whose last sale was in last 12 months

Anonymous — Thu, 09 Jun 2022 09:03:02 GMT

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)