Sales for Specific Year (DAX)

[learning_dax]

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.

[Abdulsamnad]

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. 

[ValtteriN]

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!

[learning_dax]

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))

[ValtteriN · Accepted solution]

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)

[learning_dax]

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