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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
CheenuSing
Community Champion
Community Champion

Dynamically setting the x-axis of date values based on slicer selection

Hi All,

 

I have a master calendar table with Date,Year, Month, YearMonthShortName and more

The fact data has values from Year 2013 till Year 2018 July month.

 

I have slicers for Year, Month from the calendar table.

 

I have a trend line chart of sales from fact table.

 

The requirement is like this.

 

a) If no year and month is selected the x-Axis value  of the trend line chart to be restricted to Months between Jan and July of 2018.

b) If a year is selected the x-axis value of the trend line chart should be from Jan of the selected year to the max month of the selected year.

c) if a year and month has been selected only the year & month value.

d) If two years are selected and no month then x-axis value should be from the 1st month of the minimum year to the last month of the max year.

 

And more scenarios.

 

Any help will be appreciated.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
1 ACCEPTED SOLUTION
v-lili6-msft
Community Support
Community Support

hi,@CheenuSing

    After my test, You may try to create dynamic measures to achieve your requirement a,

and b,c,d can be achieved by add year column and month column.

use the below video to do it, https://www.youtube.com/watch?v=gYbGNeYD4OY

 

and i have done a demo by it.

Result:

when i don't select any date in slicer

the x-Axis value  of the trend line chart is only year of 2016

8.PNG

then i select year 2014 ,the x-Axis value  of the trend line chart is  all the month of year 2014

9.PNG

then i select year 2014 and month Aug ,the x-Axis value  of the trend line chart is  the month Aug and the year 2014

10.PNG

 

Here is my demo, please try it

https://www.dropbox.com/s/4lj23i715okpu7f/Dynamically%20setting%20the%20x-axis%20of%20date%20values%...

 

Best Regards,

Lin

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-lili6-msft
Community Support
Community Support

hi,@CheenuSing

    After my test, You may try to create dynamic measures to achieve your requirement a,

and b,c,d can be achieved by add year column and month column.

use the below video to do it, https://www.youtube.com/watch?v=gYbGNeYD4OY

 

and i have done a demo by it.

Result:

when i don't select any date in slicer

the x-Axis value  of the trend line chart is only year of 2016

8.PNG

then i select year 2014 ,the x-Axis value  of the trend line chart is  all the month of year 2014

9.PNG

then i select year 2014 and month Aug ,the x-Axis value  of the trend line chart is  the month Aug and the year 2014

10.PNG

 

Here is my demo, please try it

https://www.dropbox.com/s/4lj23i715okpu7f/Dynamically%20setting%20the%20x-axis%20of%20date%20values%...

 

Best Regards,

Lin

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-lili6-msft

 

Many a thanks for the solution . It meets my requirement to some extent.

 

The solution works perfectly for some scenarios.

 

In one of the scenarios I need to show from the minimum of the year in the data to the selected year or till the end of the date if no year is selected.  Say I have 2014, 2015,2016,2017 andd 2018 data upto Jul.

 

When no year is selected I need to show from 2014 Jan to 2018 Jul.  WHen a year is selected then from 2014 Jan to the last month of Year selected or upto the month of the Year selected in slicer.  Under this scenario, since the slicer is already selected the x-axis used in the sample always shows for the year selected only. Trying to loop the dates table based on the Min of Year also does not work because of row context.

 

How do we go about this then.

 

Cheers

 

CheenuSing

 

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.