cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

MVP

## DAX issue with interim measure and time intelligence

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 🙂

1 ACCEPTED SOLUTION
Microsoft Employee

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

Microsoft Employee

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors