To format X-axis to be based on Page Slicer

[pooofy]

Hi,

These are the current visualisations in my dashboard.

1) Slicer for page by Date, using the "Between" Style, i.e. enter start-date, enter end-date

2) Column chart with the following

- x-axis based on Years, e.g. 2021, 2022, 2023

- Average lines based on years (e.g. total divided by total number of years)

- Columns with values for each year

I would like the chart to be filtered such that

- x-axis shows only 3 years of data --> the year of the end-date for the Slicer on the page, and the two years before

- average lines based on the 3 years shown in the chart --> total for the selected 3 years divided by the 3 years

- columns with values for each year --> no change

Could I please ask how I can get the chart to only show 3 years of data, with the last year = the max year in the page Slicer?

Thank you!!

[amitchandak]

@pooofy , if your difference between start date and end date is more than three year, slicer need to be on independent date table

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -12) +1
return
calculate( sum(sales[Gross Sales]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

//Date1 is independent Date table, Date is joined with Table
Yearly Avg  =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -12) +1
return
calculate( Averagex( Values(Date[Year]) ,calculate( sum(sales[Gross Sales]))), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

If range is more than 3 years , you use date in place of date 1 too

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI