Forum Discussion
Calculate last 4 weeks based on slicer value
Dear community,
I have been trying to figure out how to calculate data for the last 4 weeks when 1 week is selected in a slicer.
The picture below shows what result I want to get when only week 22 is selected en what result I get when using my formula.
Used formula:
Could someone help me with this problem?
Freekh
To use selectedvalue(), you need to create a distinct slicer table that is not related to the model, then use this new table column as the slicer.New Slicer Table= Distinct(dim_Date[ISOWeek])
You may add allselected() function inside the filter expression, but I am not 100% sure about your expected output, you may not need it. Give it a try:
Last4Weeks =
var SelectedWeek = SELECTEDVALUE('New Slicer Table'[ISOWeek])return CALCULATE(DISTINCTCOUNT(fct_Stops_per_Customer[AnomalyKey]),FILTER(Allselected(dim_Date), dim_Date[ISOWeek]<=SelectedWeek && dim_Date[ISOWeek]>=SelectedWeek-4 ))Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
9 Replies
m3tr01dContinued Contributor
Hello Freekh,
Let's forgot about the second slicer for the moment.
If a user select Week 22 in the top slicer, you expect the top visual to only show the data for Week 22 and you expect the bottom visual to show data only for Week 22, Week 21, Week 20, Week 19.
If the user would be selecting Week 18, you would only see in the bottom visual data for
Week 18, Week 17, Week 16, Week 15 right?
Thank you for helping usm3tr01d
In this scenario, you can only use the new table slicer by creating another measure, put it in the top visual.selected week = CALCULATE(DISTINCTCOUNT(fct_Stops_per_Customer[AnomalyKey]),FILTER(Allselected(dim_Date),dim_Date[ISOWeek]=SELECTEDVALUE('New Slicer Table'[ISOWeek])Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.Freekh
To use selectedvalue(), you need to create a distinct slicer table that is not related to the model, then use this new table column as the slicer.New Slicer Table= Distinct(dim_Date[ISOWeek])
You may add allselected() function inside the filter expression, but I am not 100% sure about your expected output, you may not need it. Give it a try:
Last4Weeks =
var SelectedWeek = SELECTEDVALUE('New Slicer Table'[ISOWeek])return CALCULATE(DISTINCTCOUNT(fct_Stops_per_Customer[AnomalyKey]),FILTER(Allselected(dim_Date), dim_Date[ISOWeek]<=SelectedWeek && dim_Date[ISOWeek]>=SelectedWeek-4 ))Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.Hi Paul,
Thanks for the reply. This solution is giving me the right result.
However, my users will still have to select the desired week in two slicers now:- 1 slicer affects most of the visuals to select 1 isoweek.
- 1 slicer filters the last 4 weeks of the selected week.
Is there anyway to make sure my users will only have to select the desired isoweek in 1 slicer?
I have the same issues. Have you already found a solution?
Thank you.
Freekh
To achieve this you can just create another slicer to effect others visuals using the dim_Date[ISOWeek] column. Basically, you may use the original week column to slice others visuals and use the new table slicer for the last 4 weeks measure.Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
