Override Slicer Filter Context in Chart (To Show Fuller X-Axis)

Question

Imagine I have a fact table with sales spanning 3 years (2016-2018). I have a chart showing sales by month (36 points on the X-Axis). I have a slicer selection to Year = 2018, and Month = June.

Is it possible, with a measure, to show on a chart, the trailing 6 months from the slicer selection? In other words, with the slicer still set to Year = 2018 and Month = January, can the chart display 6 points (the trailing 6 months)?

How would this be accomplished?

Alexis Olson Alexis Olson · Accepted Answer · 2018-08-24T15:42:07

The approach I would use in this case would be to create a parameter table for the date which doesn't have a relationship with my other tables and use that date for the slicer. Then you'd write the sales measure you use on the chart to read the selected date and return blanks for any dates not within the range you want.

Roughly like this:

NewSalesMeasure = 
    VAR SelectedDate = SELECTEDVALUE(Slicer[Date])
    VAR CurrentDate = SELECTEDVALUE(Sales[Date])
    RETURN IF(CurrentDate <= SelectedDate &&
              CurrentDate > DATEADD(SelectedDate, -6, MONTH)
              SUM(Sales[Amount]),
              BLANK()
           )

Override Slicer Filter Context in Chart (To Show Fuller X-Axis)

1 Answers