Dax command for new column with specific year sales

[Anonymous]

Good Morning,

I am attempting to write a DAX Command to create new columns for Sales from a specific year. I am attempting to compare YOY data from today back to 1998 on a line or bar graph with the Y axis being Sales $ and the X Axis being the month. I would also like to be able to filter those columns by Customer. Currently I have the following expression and it is not working correctly:

2021 Sales = CALCULATE([Total Sales], Filter( ALL(SALES), SALES[InvoiceDate].[Year]= 2021))

My Total Sales Calculation is as follows :  Total Sales = SUM(SALES[Sales])

When I run the expression for 2021 Sales, it returns the same number for every customer. I will need to copy this formula 24 times to create 24 new columsn, 1 for each year.

Any help with this would be greatly appreciated. Thank you in advance.

[littlemojopuppy]

It's returning the same number for all Customers because you used ALL() in the FILTER function.

Try this...

2021 Sales = 
	CALCULATE(
		[Total Sales], 
		FILTER(
			ALLEXCEPT(
				SALES,
				CustomerID
			), 
			SALES[InvoiceDate].[Year]= 2021
		),
		'Customer'[CustomerID] = SELECTEDVALUE('Customer'[CustomerID])
	)

assumes you have a field named CustomerID 

[Anonymous]

Unfortunately, that is returning the following error :
A function 'SELECTEDVALUE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

[littlemojopuppy · Accepted solution]

😐  Oops...that's what I get for doing this in Notepad.

This instead

2021 Sales = 
	CALCULATE(
		[Total Sales], 
		FILTER(
			ALLEXCEPT(
				SALES,
				CustomerID
			), 
			SALES[InvoiceDate].[Year]= 2021
		),
		FILTER(
			'Customer',
			'Customer'[CustomerID] = SELECTEDVALUE('Customer'[CustomerID])
		)
	)

[Anonymous]

Thank You! That worked perfect.

[littlemojopuppy]

You're welcome.  Glad I could help!  🙂