Forum Discussion

jwisrael's avatar
jwisrael
Frequent Visitor
5 years ago
Solved

Comparing Current and Past Year Sales

Hello,

 

I'm struggling with a DAX Calculation that would allow me to calculate my current years Sales for only the customers that had sales in the previous year, and who's Product Group is the same as last year. The goal is to eliminate new customers from the calculation and any new Product Groups that a customer purchased this year but not last year. Any help or guidance would be amazing.

 

Date ValueCustomer NameSalesProduct Group
1/5/2020A$1001
2/5/2020A$1501
12/5/2020B$1002
1/4/2021A$1501
1/15/2021A$501
2/20/2021A$752
3/10/2021B$1002
5/4/2021C$2001

 

Thanks,

J

Need Help
  • PaulOlding's avatar
    PaulOlding
    5 years ago

    This measure first builds a table of combinations of Customer and Product that existed last year, then applies that as a filter when calculating the sales amount.

     

     

    Like-for-like Sales =
    VAR _PreviousYearCombinations =
    CALCULATETABLE(
    SUMMARIZE(Sales, Sales[Customer Name], Sales[Product Group]),
    PREVIOUSYEAR('Date'[Date])
    )
    VAR _Result =
    CALCULATE(
    SUM(Sales[Sales]),
    FILTER(Sales,
    (Sales[Customer Name], Sales[Product Group]) IN (_PreviousYearCombinations)
    )
    )
    RETURN
    _Result

4 Replies

  • edhans's avatar
    edhans
    Community Champion
    5 years ago

    See if this works jwisrael 

    Here is the basic measure:

    Valid Sales = 
VAR varCustomer = MAX('Table'[Customer Name])
VAR varGroup = MAX('Table'[Product Group])
VAR varCurrentYear = MAX('Date'[Year])
VAR varPriorYearData = 
    CALCULATE(
        [Total Sales],
        REMOVEFILTERS('Table'),
        YEAR('Table'[Date Value]) = varCurrentYear - 1,
        'Table'[Product Group] = varGroup,
        'Table'[Customer Name] = varCustomer,
        DATEADD('Date'[Date], -1, YEAR)
    )
RETURN
    varPriorYearData

    It returns the "Valid Sales" column:

    You need a date table to do this - which you can get from here - and note the date table is marked as a date table in the model. https://bit.ly/DateTableByEd

    My PBIX file is here.

     

    • jwisrael's avatar
      jwisrael
      Frequent Visitor
      5 years ago

      Thank you for the detailed response! What table would I use for REMOVEFILTERS('Table')?

      • edhans's avatar
        edhans
        Community Champion
        5 years ago

        Your sales table. When I pasted the data in to Power BI, it was just called "Table" so that is what I used in the REMOVEFILTERS. In reality it would be REMOVEFILTERS(Sales) or REMOVEFILTERS('Sales Table') or whatever you call it. I have to do that to be able to access the entire table when applying the customer, group, and then last year's year number to it.

         

        If this helped, please mark it as the solution.

  • PaulOlding's avatar
    PaulOlding
    Solution Sage
    5 years ago

    This measure first builds a table of combinations of Customer and Product that existed last year, then applies that as a filter when calculating the sales amount.

     

     

    Like-for-like Sales =
    VAR _PreviousYearCombinations =
    CALCULATETABLE(
    SUMMARIZE(Sales, Sales[Customer Name], Sales[Product Group]),
    PREVIOUSYEAR('Date'[Date])
    )
    VAR _Result =
    CALCULATE(
    SUM(Sales[Sales]),
    FILTER(Sales,
    (Sales[Customer Name], Sales[Product Group]) IN (_PreviousYearCombinations)
    )
    )
    RETURN
    _Result