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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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))

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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