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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.