This is doable but it requires some tricks and extra measures.
TL;DR: The slicer you see is actually served as a value picker
, not as a filter. An extra measure based on the value
is created and used as visual level filter
for the visual to do the trick.
If you want to follow along, you can download the .pbix file from this Microsoft edX course about Power BI.
First, create a new table
based on the existing Date
table, with only distinct years:
Year = DISTINCT('Date'[Year])
Then, create a slicer with the Year
column from the newly created Year
table (NOT the Date
table).
A measure (used as flag) is created as follows:
Flag =
VAR YearSelected = FIRSTNONBLANK(VALUES('Year'[Year]), 0)
RETURN
IF(VALUES('Date'[Year]) >= YearSelected, 1, 0)
So basically it gets the year selected from the year slicer and compare it with the year value in the date table to see if it's greater than or equal to it.
The chart is created with Year
column from the Date
table (NOT the Year
table), and other needed measures. Flag
is added to the Visual level filters
and set to 1.
So the Flag
value will change according to the value picked in the Year slicer, and served as the actual filter to the chart displayed.
Results:
EDIT: on more use cases
@Oleg Try to think of how you can apply the Flag
concept further. For example, if you want another chart displaying data of the same year as the slicer, you can set up another flag called SameYearFlag
and only change the part of value comparison to =
. Add it to the chart Visual level filter
and it'll show only data in the same year. Yes, by extension, that means you can have another flags like LastYearFlag
, NextYearFlag
, etc, as long as it makes sense to you. The use case is up to you.
LastYearFlag =
VAR YearSelected = FIRSTNONBLANK(VALUES('Year'[Year]), 0)
RETURN
IF(YearSelected - VALUES('Date'[Year]) = 1, 1, 0)
NextYearFlag =
VAR YearSelected = FIRSTNONBLANK(VALUES('Year'[Year]), 0)
RETURN
IF(VALUES('Date'[Year]) - YearSelected = 1, 1, 0)
SameYearFlag =
VAR YearSelected = FIRSTNONBLANK(VALUES('Year'[Year]), 0)
RETURN
IF(VALUES('Date'[Year]) = YearSelected, 1, 0)
Examples:
By having only one year slicer, I can have charts with data in the same year, last year, next year and all years following, by applying different flags to them.
As said, it's up to you to come up with more interesting use cases!