Created a measure/column interacting with selected MonthYear to be used as filter on visual

[RobinVanBeijnum]

Hi all,

I have been looking for a solution and found some hints to possibilities, but still struggle with getting it done!

The goal:

We would like to have an additional attribute that we can use to filter a visual instead of creating a ton of additional measures to achieve the result.

The setup: 

We have a slicer where the user will select one (single select) MonthYear from a date dimension (also marked as the date table). We have around 20 line charts (currently, still growing). Depending on the selected value in the slicer, we want to show the last 13 months on the axis of these charts. At the same time, the page contains card visuals that show the value for the current month only (this part is straightforward 😃). Instead of creating a ton of measures, we would like to have an attribute (either a calculated column or one additional measure) we can use as a filter for the line charts and disable the interaction between the slicer and the line charts (else we end up with one period in the "line" chart).

Is this possible? If it is, how? 😁

Our data is in the form of a dimensional model. All reported numbers come from one fact table. 

[rajendraongole1]

Hi @RobinVanBeijnum  - Yes! You can achieve this in Power BI without creating multiple measures by using a calculated column or a measure that dynamically filters the last 13 months based on selection.

First step, create a Calculated Column in your Date Table to Flag the Last 13 Months.
use the Calculated Column as a Visual Filter for the Line Charts.disable Interaction Between the Slicer and Line Charts so the slicer doesn’t filter them.

suppose, if you prefer to use measure as follows for 13months. 

IsLast13MonthsMeasure =
VAR SelectedMonth = SELECTEDVALUE('DateTable'[MonthYear])
VAR SelectedDate = MAXX(FILTER(ALL('DateTable'), 'DateTable'[MonthYear] = SelectedMonth), 'DateTable'[Date])
RETURN
IF(
MAX('DateTable'[Date]) >= EDATE(SelectedDate, -12) && MAX('DateTable'[Date]) <= SelectedDate,
1,
0
)

Apply this as a visual-level filter in your line charts, keeping only values where IsLast13MonthsMeasure = 1.

[RobinVanBeijnum]

Hello rajendraongole1,

Thanks for the reply. If I create the measure and disable interaction between the slicer and e.g. a table, the table returns 1 for every Month_Year, not only the months that should be in scope (selected month and the previous 12 months, for testing purposes I even made it 1 prior month, hence the -1). What am I missing here?