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:
Thank you very much, the measure works very well. I have tried to use this measure by modifying it to create two new measures: NEW CUSTOMERS AND LOST CUSTOMERS. I am not able to obtain the desired results, could you please continue helping me?

  • NEW CUSTOMERS: CUSTOMERS WITH SALES IN THE CURRENT FISCAL YEAR AND NO SALES IN THE PREVIOUS TWO FISCAL YEARS
  • LOST CUSTOMERS: CUSTOMERS WITH NO SALES IN THE LAST THREE FISCAL YEARS
    If it's not too much to ask, would you be so kind to detail the code for both measures? Do I need to open a new post to ask you these new questions?
    Thank you in advance for your help.
Fowmy's avatar
Fowmy
Super User
2 years ago

joseluis1969240 

Measure for new customers:

New Customers =
VAR YearMax =
    MAX ( 'Date'[Fiscal Year] )
VAR Actual_Customer =
    CALCULATETABLE (
        VALUES ( 'Sales'[CustomerKey] ),
        'Date'[Fiscal Year] = YearMax
    )
VAR Actual_Customer_1Yr =
    CALCULATETABLE (
        VALUES ( 'Sales'[CustomerKey] ),
        'Date'[Fiscal Year] = YearMax - 1
    )
VAR Actual_Customer_2Yr =
    CALCULATETABLE (
        VALUES ( 'Sales'[CustomerKey] ),
        'Date'[Fiscal Year] = YearMax - 2
    )
VAR Year1_2 =
    DISTINCT ( UNION ( Actual_Customer_1Yr, Actual_Customer_2Yr ) )
VAR Result =
    EXCEPT ( Actual_Customer, Year1_2 )
RETURN
    CALCULATE ( [Sales Amount], Result )


Measure for lost customers:

Lost Customers =
VAR YearMax =
    MAX ( 'Date'[Fiscal Year] )
VAR Result =
    SUMX (
        VALUES ( Customer[CustomerKey] ),
        VAR __Sales3yrs =
            CALCULATE (
                [Sales Amount],
                'Date'[Fiscal Year]
                    IN {
                        YearMax,
                        YearMax - 1,
                        YearMax - 2
                    }
            )
        VAR __Salesbefore3yrs =
            CALCULATE ( [Sales Amount], 'Date'[Fiscal Year] < YearMax - 2 )
        RETURN
            IF ( ISBLANK ( __Sales3yrs ), __Salesbefore3yrs )
    )
RETURN
    Result



  • joseluis1969240's avatar
    joseluis1969240
    Frequent Visitor
    2 years ago

    Good afternoon:

    Thank you again for your help. The measure that calculates sales to new customers works perfectly; however, I'm having trouble implementing the measure that calculates sales from lost customers. Power Pivot is giving me the following error:

     

    As you can see, I've copied the measure you provided (adjusting the field names accordingly). Could you assist me again?
    Thanks in advance.

    • Fowmy's avatar
      Fowmy
      Super User
      2 years ago

      You missed a bracket:
      IF ( ISBLANK ( __Sales3yrs ), __Salesbefore3yrs )

      • 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,