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
Anonymous
Not applicable

Trending chart based on cut off date (Slicer)

Hi All,

I'm trying to create a trending chart based on a cut off date for a financial year period. Meaning if YTD is from January to December, my trending chart will always show the amount from Jan - cut off month based on slicer. Eg, I select October, My chart will show Jan - Oct figure (not cumulative).

Does anyone have any idea how? I've tried several method that other people posted but can't get the amount right. Fyi, I sync my slicer for each page using my Calendar table. Do I need to create a separate calendar for it? If yes, how to sync it with other pages?

Thanks a lot!

1 ACCEPTED SOLUTION
v-rzhou-msft
Community Support
Community Support

Hi @Anonymous 

I think you want to show values in all month beforan or equal to the month you select.

In this case you need to build an unrelated table for slicer.

My Sample:

1.png

Slicer table:

2.png

Then build a measure as below.

 

Measure = 
VAR _SelectMonth = SELECTEDVALUE(Slicer[MonthNumber])
Return
IF(ISFILTERED(Slicer[MonthName]),CALCULATE(SUM(Sheet53[Sales]),FILTER(Sheet53,Sheet53[MonthNumber]<=_SelectMonth)),SUM(Sheet53[Sales]))

 

Result is as below.

By default it will show values of whole year.

3.png

When I  select Oct, it will show values from Jan to Oct.

4.png

This slicer will only impact the visuals which contains the measures built with it.

So you need to build measure filters as below to impact other visuals in this report by sync slicer.

 

Measure Filter = 
IF(MAX(Sheet53[MonthNumber])<=SELECTEDVALUE(Slicer[MonthNumber]),1,0)

 

Add this measure into the visuals you want to impact by this slicer and set the measure to show items when value =1.

Here I use sync slicer in Slicer in Page one, and my visual in Page 2 will be filtered.

2.png3.png

You can download the pbix file from this link: Trending chart based on cut off date (Slicer)

 

Best Regards,

Rico Zhou

 

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

3 REPLIES 3
v-rzhou-msft
Community Support
Community Support

Hi @Anonymous 

I think you want to show values in all month beforan or equal to the month you select.

In this case you need to build an unrelated table for slicer.

My Sample:

1.png

Slicer table:

2.png

Then build a measure as below.

 

Measure = 
VAR _SelectMonth = SELECTEDVALUE(Slicer[MonthNumber])
Return
IF(ISFILTERED(Slicer[MonthName]),CALCULATE(SUM(Sheet53[Sales]),FILTER(Sheet53,Sheet53[MonthNumber]<=_SelectMonth)),SUM(Sheet53[Sales]))

 

Result is as below.

By default it will show values of whole year.

3.png

When I  select Oct, it will show values from Jan to Oct.

4.png

This slicer will only impact the visuals which contains the measures built with it.

So you need to build measure filters as below to impact other visuals in this report by sync slicer.

 

Measure Filter = 
IF(MAX(Sheet53[MonthNumber])<=SELECTEDVALUE(Slicer[MonthNumber]),1,0)

 

Add this measure into the visuals you want to impact by this slicer and set the measure to show items when value =1.

Here I use sync slicer in Slicer in Page one, and my visual in Page 2 will be filtered.

2.png3.png

You can download the pbix file from this link: Trending chart based on cut off date (Slicer)

 

Best Regards,

Rico Zhou

 

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

Anonymous
Not applicable

Thank you @v-rzhou-msft ! 

Just got the time to properly check this solution as I was focusing on other things and it works exactly as I wanted. Appreciate it!

amitchandak
Super User
Super User

@Anonymous , Not very clear. If you are selecting one date and want to show more than one date then you need an independent date table

example video -https://www.youtube.com/watch?v=duMSovyosXE

 

in case you need to change FY in Datesytd , you can try like

 

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"10/31"))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

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.