Filter bar chart by selected values and two previous ones

[rode]

Hi!

I have to implement a behaviour like this: if I select a year in the slicer (which works on the time dimension table), the bar chart should show me the values only for the selected year and the previous two dynamically. For example, if I select 2019 in the slicer, the chart must show 2017, 2018 and 2019. If I select 2018, the chart must show 2016, 2017 and 2018. And so on. The bar chart has the year in the X-axes.

Let's say Z the value set in the slicer. I've tried by using a calculated column on the dimension table in order to implement a flag that is TRUE only if the row is between Z and Z-2, but I don't succeed to retrieve Z. Is it possible to do that?

Thank you!

[Ashish_Mathur]

Hi,

I have solved a similar problem here - Flex a Pivot Table to show data for x months ended a certain user defined month.

Hope this helps.

[rode]

Hi @v-frfei-msft, @Ashish_Mathur and thank you very much for your help!

I've found out in both your solutions that a time dimension table not connected to the fact table is used.

My requirement is the following: in order to keep the slicers on the time dimension syncronized with other pages, I must apply the slicer on the time dimension table that is connected to the fact table. So, the dimension table (on which the slicer is built) filters the fact table, but when I select one year, the bar chart should show me three years (ending with the selected one) instead of one.

Is it possibile to achieve a result like this?

Thank you very much,

Regards.

[v-frfei-msft]

Hi @rode,

Actually it is not possible, because once you choose any value in the slicer, then the table will be filered accrodingly. You can only query the data in the filtered table. By the way, why did you can only use the dimension table to build the slicer?

Regards,

Frank

[rode · Accepted solution]

The need of using the field coming from the time dimension is related to a synchronization matter among all the year slicers in the reports.

However I'm using an alternative solution. I've created a new dimension table as following:

===================

== Year_In  ==  Year_Out ==

== 2019     ==  2019        ==

== 2019     ==  2018        ==

== 2019     ==  2017        ==

== 2018     ==  2018        ==

== 2018     ==  2017        ==

== 2018     ==  2016        ==

== 2017     ==  2017        ==

== 2017     ==  2016        ==

== 2017     ==  2015        ==

==    .... and so on....        ==

==================

This table has a relation via YearOut column to the "normal" time dimension table.

I've added a calculated column on the new dimension table:

Flag_CY_only := IF([Year_in] = [Year_Out]; TRUE(); FALSE())

All the year-based slicers now refer to the Year_In column of the new dimension table. In the pages of the reports that must use only the current year (e.g. if the user selects 2019, only 2019 must be shown), there is a background filter on Flag_CY_only = True. Instead, in the pages that must work with last 3 years logic (e.g. if the user selects 2019, then 2017, 2018 and 2019 must be shown), no background filter is used.

[v-frfei-msft]

Hi @rode,

I made one sample for your reference. Please check the following steps as below.

1. Create a calculated table.

Table = VALUES(Table1[year])

2. Create a measure as below.

amount = var total = SUM(Table1[value])
var _Year = SELECTEDVALUE('Table'[year])
var _currentY = MAX('Table1'[year])
return
IF(_currentY<=_Year && _currentY>= _Year-2,total,BLANK())

For more details, please check the pbix as attached.

Regards,

Frank