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...
joseluis1969240's avatar
joseluis1969240
Frequent Visitor
2 years ago

Good afternoon again:

I've just corrected the error you pointed out, but I'm still facing an issue: the measure doesn't support filters, meaning if I create a pivot table with the 'customer' field and the "lost customers" measure, and try to filter a specific year (for example: 2024), the pivot table doesn't display any data... Could this measure be modified to show results when filters are applied? Lastly, I would like to ask if it's possible to dynamically determine the number of lost customers as well as the amount of their sales.

Thank you again for your patience. Best regards,

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