Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
pooofy
Helper I
Helper I

To format X-axis to be based on Page Slicer

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!!

1 REPLY 1
amitchandak
Super User
Super User

@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

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.