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

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