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
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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors