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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. 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))

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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