topic Re: Sales for Specific Year (DAX) in DAX Commands and Tips

Sales for Specific Year (DAX)

learning_dax — Thu, 20 Jan 2022 08:30:12 GMT

Hi all,

Newbie to DAX, so this might be a simple one. I'm simply using SuperStore dataset with a Date table trying to calculate JUST 2016 sales when the Order Date was in 2016. Trying to create a measure to put in a table/pie chart in which I can only get the sales for the orders placed in 2016. My work-around, I think, was using CALCULATE then the DATESBETWEEN function Jan 1 2016 - Dec 31 2016 (the relationship is from the Date table to the Orders table Order Date). When I try to use CALCULATE [TotalSales] then Filter 'Date'[Date] = 2016 or 'Orders'[Order Date] = 2016 I get blanks. I believe I've done this before but I may be wrong. Hope I'm not overcomplicating things.

Re: Sales for Specific Year (DAX)

ValtteriN — Thu, 20 Jan 2022 08:39:17 GMT

Hi,

You just need to add YEAR('Date'[Date]) instead of 'Date'[Date] and you are good to go. E.g. CALCULATE([measure],ALL(Table),YEAR('Date'[Date])=2016))

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

Re: Sales for Specific Year (DAX)

learning_dax — Thu, 20 Jan 2022 08:54:25 GMT

Hi ValtteriN,

Thanks for the response. I used the formula you suggested and it simply gave me the aggregate amount of sales for all years. It is not filtering for 2016. This does not change even if I exclude the ALL function nor if I change 'Date'[Date] = 2016 or 'Orders'[Order Date] = 2016. I thought there was a way to do this as you suggested but I'm not getting filtered output.

Calculations I tried:

CALCULATE([TotalSales],YEAR(Orders[Order Date] = 2016))

CALCULATE([TotalSales],YEAR('Date'[Date] = 2016))

CALCULATE([TotalSales],ALL(Orders),YEAR('Date'[Date] = 2016))

Re: Sales for Specific Year (DAX)

ValtteriN — Thu, 20 Jan 2022 09:02:13 GMT

Hi,

YEAR() needs to encompass the date so YEAR('Date'[Date])=2016. In the examples you posted the bracket is outside e.g. ,YEAR('Date'[Date] = 2016)

Re: Sales for Specific Year (DAX)

learning_dax — Thu, 20 Jan 2022 09:05:32 GMT

That worked. Little things like that I'm still getting used to. Thank you so much!

Re: Sales for Specific Year (DAX)

Abdulsamnad — Tue, 02 Jan 2024 12:52:55 GMT

i tired this equation and i got the result but when i draged this measure into visual where i want to show monthly wise i am getting the same amount till December.