topic Re: Comparing Current and Past Year Sales in DAX Commands and Tips

Comparing Current and Past Year Sales

jwisrael — Mon, 10 May 2021 19:25:13 GMT

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,

Re: Comparing Current and Past Year Sales

edhans — Mon, 10 May 2021 23:32:19 GMT

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.

Re: Comparing Current and Past Year Sales

jwisrael — Tue, 11 May 2021 01:28:45 GMT

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

Re: Comparing Current and Past Year Sales

edhans — Tue, 11 May 2021 03:08:15 GMT

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.

Re: Comparing Current and Past Year Sales

PaulOlding — Tue, 11 May 2021 09:18:11 GMT

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