Forum Discussion

joseluis1969240's avatar
joseluis1969240
Frequent Visitor
2 years ago

Recurring Customers

Good morning, I'm trying to define a measure in DAX that provides me with the amount of recurring sales per customer for each fiscal year I choose. In my case, recurring sales for, let's...
Fowmy's avatar
Fowmy
Super User
2 years ago

joseluis1969240 

Please try this measure for lost customers, it shows the count. I am not sure if the amount makes sense here.

Lost Customers = 
Var YearMax = MAX('Date'[Fiscal Year])
var Result = 
    SUMX(
        VALUES( Customer[CustomerKey] ),
        VAR __Sales3yrs = 
            CALCULATE(
               COUNTROWS( VALUES(Sales[CustomerKey] ) ),
                'Date'[Fiscal Year] IN { YearMax , YearMax-1  ,  YearMax-2    }        
            )
        VAR __Salesbefore3yrs = 
           CALCULATE(
               COUNTROWS( VALUES(Sales[CustomerKey] ) ),
                'Date'[Fiscal Year] <  (YearMax-2)
            )
        
        RETURN 
            IF( __Sales3yrs <> 1 && __Salesbefore3yrs =1 ,  1 )
    )
RETURN
    Result
joseluis1969240's avatar
joseluis1969240
Frequent Visitor
2 years ago

Good morning:
The measure you mentioned works perfectly, but I need it to work dynamically. Let me explain: if I generate a pivot table with the 'customer name' field and the 'lost customers' measure, the pivot table's result is correct. HOWEVER, if I apply a filter to the pivot table by 'fiscal year,' the table appears without data. Could you please help me modify the measures you've taught me to make them work when I apply filters by year in pivot tables? These are the last two measures you provided and that I need to function when I apply filters to the pivot tables in which I use them:

 

Thank you very much again