CODEWITHSUNDEEP
powerbidax
JJB
JJB

Reputation: 53

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

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?

Upvotes: 0

Views: 534

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40224

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()
           )

Upvotes: 0

Related Questions