Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Dax command for new column with specific year sales

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.
1 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])
		)
	)

View solution in original post

5 REPLIES 5
littlemojopuppy
Community Champion
Community Champion

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
Not applicable

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.

😐  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
Not applicable

Thank You! That worked perfect.

You're welcome.  Glad I could help!  🙂

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.