Comparing Current and Past Year Sales

[jwisrael]

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 Value	Customer Name	Sales	Product Group
1/5/2020	A	$100	1
2/5/2020	A	$150	1
12/5/2020	B	$100	2
1/4/2021	A	$150	1
1/15/2021	A	$50	1
2/20/2021	A	$75	2
3/10/2021	B	$100	2
5/4/2021	C	$200	1

Thanks,

J

[PaulOlding · Accepted solution]

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]

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]

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

[edhans]

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.