Skip to main content
cancel
Showing results for 
Search instead 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

Reply

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
v-micsh-msft
Employee
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:

21.PNG22.PNG23.PNG

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

Regards

View solution in original post

1 REPLY 1
v-micsh-msft
Employee
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:

21.PNG22.PNG23.PNG

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

Regards

Helpful resources

Announcements
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

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