Previous value in Filter context (Filtered context)

[razvancanuta]

Hello guys,

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

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

[amitchandak]

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

[razvancanuta]

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:

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

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.

Many thanks

[v-jingzhang · 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)

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

[razvancanuta]

Hi @v-jingzhang, thanks for your response.

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

Many thanks!