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
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
Solved! Go to Solution.
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 , 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:
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
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.
Hi @v-jingzhang, thanks for your response.
ALLSELECTED was the solution, I completely forgot about this one!
Many thanks!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
104 | |
103 | |
87 | |
61 |
User | Count |
---|---|
168 | |
138 | |
134 | |
102 | |
86 |