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

Reply
razvancanuta
Frequent Visitor

Previous value in Filter context (Filtered context)

Hello guys,

As the title says I have to calculate the previous value in filter context, as follows:

razvancanuta_0-1658927663471.png

On the dashboard I have a filter for the Period column and the user should be able to select any and as many "Week x Season x" as  he wants.
So is there a way to determine the previous value in a filtered context?

Many thanks

 



1 ACCEPTED SOLUTION

Hi @razvancanuta

 

To get the previous value of a week, we need to know how these weeks are sorted (or ranked). According to the data you showed, it seems we cannot distinguish the week order according to the week names.

 

Assume you have a WeekRank column in your original table, you can create the following measure to get the previous value in the current context. Note that ALLSELECTED function is used to keep the filter from the slicer outside. 

Previous Value = 
VAR _currentWeekRank = SELECTEDVALUE(SampleTable[WeekRank])
VAR _previousWeekRank = CALCULATE( MAX(SampleTable[WeekRank]), ALLSELECTED(SampleTable[WeekName]), SampleTable[WeekRank] < _currentWeekRank)
RETURN
CALCULATE( SUM(SampleTable[Value]), ALLSELECTED(SampleTable[WeekName]), SampleTable[WeekRank] = _previousWeekRank)

vjingzhang_0-1659346533850.png

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@razvancanuta , You need to Serial number or rank if the are sortable

 

example

a new column - Move period to a new table say week and create rank on sortable column

Week rank = rankx(Week, [Period],,asc, dense) 

 

 

new measures

This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

Hello @amitchandak and thank you for your response!

But its not quite what I need.

If I apply a rank on my dataset, a small sample would look like this:

razvancanuta_0-1658991019502.png


The issue comes when I apply a filter on the WeekName, and the context will change like this :

razvancanuta_1-1658991093829.png

Now, the rank will not make much sense, because I dont need the previous value from the dataset (I think that can be achiveble with Lastnonblank), I need the previous value from my filtered context.

razvancanuta_0-1659000157224.png

 

Many thanks

Hi @razvancanuta

 

To get the previous value of a week, we need to know how these weeks are sorted (or ranked). According to the data you showed, it seems we cannot distinguish the week order according to the week names.

 

Assume you have a WeekRank column in your original table, you can create the following measure to get the previous value in the current context. Note that ALLSELECTED function is used to keep the filter from the slicer outside. 

Previous Value = 
VAR _currentWeekRank = SELECTEDVALUE(SampleTable[WeekRank])
VAR _previousWeekRank = CALCULATE( MAX(SampleTable[WeekRank]), ALLSELECTED(SampleTable[WeekName]), SampleTable[WeekRank] < _currentWeekRank)
RETURN
CALCULATE( SUM(SampleTable[Value]), ALLSELECTED(SampleTable[WeekName]), SampleTable[WeekRank] = _previousWeekRank)

vjingzhang_0-1659346533850.png

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Hi @v-jingzhang, thanks for your response.

ALLSELECTED was the solution, I completely forgot about this one!

 

Many thanks!

Helpful resources

Announcements
Europe Fabric Conference

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.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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