Trending chart based on cut off date (Slicer)

[Anonymous]

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!

[v-rzhou-msft · Accepted solution]

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:

Slicer table:

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.

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

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.

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]

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]

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