Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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

1 REPLY 1
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

## Helpful resources

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors