Calculate last 4 weeks based on slicer value

[Freekh]

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:

Last4Weeks =

var SelectedWeek = SELECTEDVALUE(dim_Date[ISOWeek])

return CALCULATE(

DISTINCTCOUNT(fct_Stops_per_Customer[AnomalyKey]),ALL(dim_date),FILTER(dim_Date,dim_Date[ISOWeek]<=SelectedWeek && dim_Date[ISOWeek]>=SelectedWeek-4 ))

Could someone help me with this problem?

[Anonymous]

@m3tr01d 

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.

[m3tr01d]

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 us

[Anonymous]

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

[Anonymous · Accepted solution]

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

[Freekh]

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?

[San1]

I have the same issues. Have you already found a solution?

Thank you.

[Freekh]

Hi San1,

I have a second date table in my model and used a slicer on this table with a weekoffset which filters the last week. With the formule below I can show the last 4 weeks.

Last4Weeks =
VAR SelectedWeek =
    SELECTEDVALUE ( dim_DateLastNWeeks[ISOWeek] )
RETURN
    CALCULATE (
        MEASURE(),
        FILTER (
            dim_Date,
            dim_Date[ISOYearOffset] = SELECTEDVALUE ( dim_DateLastNWeeks[ISOYearOffset] )
                && dim_Date[ISOWeek] <= SelectedWeek
                && dim_Date[ISOWeek] > SelectedWeek - 4
        )
    )

[San1]

Thank you for your reply. 

This means that the users have to fill in two slicers? One for most of the visuals and one for the calculation of the last 4 weeks?

[Freekh]

If all measures you use on the page have the same filter expression in the calculate. You could only use the dim_DateLastNWeeks date table. However this means a lot of work. I did this for one report specifically.

If you do include this in your measures, users will have to use two filters.