Limiting x-axis in the Clustered Column chart

[rile14]

Hi All,

Currently, I have a chart like this:

Is it possible to have a chart showing years on the x-axis 5 years prior and 5 years after the year where I have data? I need that to be dynamic based on the selection.

To make it more simple, I need output like this:

So, if I had data available for 1990 for example I would like to see that data displayed with a bar, and 1985 and 1995 displayed on the x-axis, together with 1990.

Thanks.

[Anonymous · Accepted solution]

Hi  @rile14 ,

I created some data:

Here are the steps you can follow：

1. Create measure.

Flag =
var _1=CALCULATE(MAX('Table'[year]),FILTER(ALL('Table'),'Table'[year]=MAX('Table'[year])&&'Table'[amount]<>BLANK()))
var _table=FILTER(ALL('Table'),'Table'[amount]<>BLANK())
return
IF(
MAX('Table'[year]) in SELECTCOLUMNS(_table,"1",[year])|| MAX('Table'[year]) in SELECTCOLUMNS(_table,"1",[year] +5)|| MAX('Table'[year]) in SELECTCOLUMNS(_table,"1",[year] -5),1,0)

2. Place [Flag]in Filters, set is=1, apply filter.

3.  Result:

Please click here for the pbix file

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[rile14]

Hi Liu,

Thanks for your response. It seems that you didn't understand my requirement. Sorry if I explained that bad.

This is the visual that I currently have:

The expected output would be to have 5 year periods shown on the x-axis starting from 1975 until 2010 (including 2010),  even though there is no data for 1975 and 2010. So this would be the result:

Is this possible? 

Thanks.

[Anonymous]

Hi  @rile14 ,

I created some data:

Here are the steps you can follow：

1. Create calculated table.

Table 2 =
DISTINCT('Table'[Year])

2. Create measure.

Flag =
var _select=SELECTEDVALUE('Table 2'[Year])
return
IF(
    YEAR(MAX('Table'[Date]))>=_select-5&&YEAR(MAX('Table'[Date]))<=_select+5,1,0)

3. Place [Flag]in Filters, set is=1, apply filter.

4. Result:

Please click here for the pbix file

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly