cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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

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

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors