DAX issue with interim measure and time intelligence

[Datatouille]

Hello everyone,

I have a problem with a DAX measure.

Let me explain :

I have a Sales table (including a column named "Sales") and a classic Calendar table (including a column named "Year") in my model.

I have a measure which computes the sum of sales. [Sum Of Sales] = Sum([Sales]).

Then, I created a measure which computes the sum of sales for last year when the user chooses a particular year in a slicer (from calendar Table).

My measure is : 

[Sum Of Sales LY] = Calculate ( [Sum of Sales] , Filter ( All (Calendar[Year]) , Calendar[Year] = Max ( Calendar[Year] ) - 1 ))

And it works properly.

I wanted to create an interim measure which stores the year chosen by the user when slicing. This measure is :

[Choosen Y] = Max ( Calendar[Year] )

My problem is : When I want to use this interim measure in [Sum of Sales LY], it simply doesn't work!

[Sum Of Sales LY] = Calculate ( [Sum of Sales] , Filter ( All (Calendar[Year]) , Calendar[Year] = [Choosen Y] - 1 )) doesn't compute anything. 

Edit : After trying a few things, It is because of the All function used in the "Table" argument of Filter.  

Alone, [Choosen Y] always correctly computes the year choosen by the user.

But in [Sum Of Sales LY] measure, [Choosen Y] is always equal to 2023 (the maximum year in my calendar table) no matter what the user chooses (because of all function used before).

I would expect [Choosen Y] to be always computed separately i.e without taking into account the previous "All" function.

Any ideas on how I can solve this issue (apart from using disconnected tables with years) ?

Precisions : I am using PowerPivot in Excel 2016 and my Year column is not a text column !

Cheers 🙂

[v-micsh-msft · Accepted solution]

Hi Excelside,

We could just create another year column, add 1 to the existing year, then use this new column as the slicer. This should work.

 Yearslicer = Calendar[Year]  + 1 

In addition, if you have a calendar table, then we could switch the lastyearsales formula with the following one:

[Sum Of Sales LY] = Calculate ( [Sum of Sales], DATEADD(DATESYTD('Calendar'[Date]),-1,Year))

See the result from my side:

If any further assistance needed, please feel free to post back.

Regards