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.
Anonymous's avatar
Anonymous
Not applicable
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
5 years ago

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

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

     

    Thank you.

    • 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?