How to see the total for the same months last year?

[afaro]

I have a year slicer at the top from which specific year can be chosen. 
I also have a bar chart which has the month name on the x axis. Now, I want to see the total for every month but for the previous year to the year selected in the slicer.

I wrote the following DAX which is given me total sum for chosen year instead. Doesn't even filter the month. What is wrong in the logic here? 
1 total last year = CALCULATE(sales[total], FILTER(ALLEXCEPT(datetable, datetable[months-name]), datetable[year] = MAX(datetable[year])-1))

I know I can write a more simple working solution like this: 

2 total last year = CALCULATE(sales[total], datetableyear[year] = MAX(datetable[year])-1))
But, for my understanding, I want to know why the 1  doesn't work? 

[Joe_Barry · Accepted solution]

Hi @afaro 

Why not try the following few measures if you have a calander table in your data model,.

Base Sales Measure

Sales = SUM(Sales[Total])

YTD Sales

Sales - YTD = TOTALYTD([Sales], Calendar[Date])

PY Sales

Sales - PY =
CALCULATE([Sales - YTD], DATEADD(Calendar[Date], -1, YEAR))

Joe

If you found my answer helpful and it solved your issue, please accept as solution

[Anonymous · Accepted solution]

Hi, @afaro 

The problem with the first formula is the use of functions. Retains filters for all columns except the specified columns. In this case, you keep the filter, but remove all other columns (including .which is why your formula isn't filtered by the previous year as expected.) Instead, it calculates the total sales for all years, but doesn't filter by month as you might expect.

The correct logic you're looking for is to calculate the total sales for the same month but the previous year relative to the year selected in the slicer. Your second formula is more in line with this goal

However, to understand why the first formula doesn't work as intended, it's crucial to grasp how things work in a function. In your scenario, you want to keep the month filter, but move to the previous year, which isn't effectively communicated in the first formula.

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Joe_Barry · Accepted solution]

Hi @afaro 

Why not try the following few measures if you have a calander table in your data model,.

Base Sales Measure

Sales = SUM(Sales[Total])

YTD Sales

Sales - YTD = TOTALYTD([Sales], Calendar[Date])

PY Sales

Sales - PY =
CALCULATE([Sales - YTD], DATEADD(Calendar[Date], -1, YEAR))

Joe

If you found my answer helpful and it solved your issue, please accept as solution

[lbendlin]

Have you considered using SAMEPERIODLASTYEAR instead?