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 sam...
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
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.