Forum Discussion

Freekh's avatar
Freekh
Frequent Visitor
5 years ago
Solved

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 we...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.
San1's avatar
San1
Frequent Visitor
3 years ago

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's avatar
Freekh
Frequent Visitor
3 years ago

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.