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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors