cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aliah-
Frequent Visitor

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
RicoZhou
Community Support
Community Support

Hi @aliah- 

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
RicoZhou
Community Support
Community Support

Hi @aliah- 

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

aliah-
Frequent Visitor

Thank you @RicoZhou ! 

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 IV
Super User IV

@aliah- , 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.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

html:
Carousel MBAS ARun Part 1 768x460

MBAS 2021

Join Arun Ulagaratchagan and the Power BI team at MBAS! #MSBizAppsSummit #CommunityRocks

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!