Selected Year as a filter value in a Trend

[Vallirajap]

Hii All, 

I stucked with one scenario. 
I have a month slicer in MMM-YY format with single select. when i choose the slicer to get the respective whole year value in a monthly trend

For Example : When i select any months in the year 2024 (ex: Mar-24) means then all the values of year 2024 will return in a table like below

Same like all the years based on the selected month.
Thanks in advance..!

[v-xuxinyi-msft · Accepted solution]

Hi @Vallirajap 

Thanks for the reply from vojtechsima and Rupak_bi .

@Vallirajap , Do you need to display all values ​​of the same year as the selected content in the visualization chart based on the slicer? If so, the following test is for your reference:

My sample:

Calendar table:

Data table:

no relationship between two tables

Create a measure as follows

Measure = CALCULATE(SUM('Table'[Value]), YEAR('Table'[Date]) = YEAR(SELECTEDVALUE('Calendar'[Date])))

Output:

In my Line chart, the [Date] column is used as the x-axis, not the [Month] column.

Best Regards,
Yulia Xu

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

[Rupak_bi]

plz share sample data

[vojtechsima]

Hello, @Vallirajap ,

this is not really easy, but you can try this:
twoCalendarSolution.pbix

essentially, you have 2 calendars:

you write measure then like this:

sumSpecial = 
var year = YEAR( MAX(calendar_helper[Date]) )
var result = 
CALCULATE(
    [sum],
    year(calendar_helper[Date]) = year,
    USERELATIONSHIP(calendar_helper[Date], 'calendar'[Date])
)

return result

Your page looks like this;

Slicer is from Calendar_Helper, table has Date from regular Calendar

[Vallirajap]

Hi @vojtechsima, 

Thanks for the reply.
There are alternative methods available without the need for a third date table.
In our model, one calendar table and a fact table are there. We can't add another table because it affects our data model.

Can you please explain any other option to do the same? 

Thanks in advance.

[vojtechsima]

Hi, @Vallirajap ,

there's alternative.

sumSpecial = 
var year = YEAR( MAX('calendar'[Date]) )
var result = 
CALCULATE(
    [sum],
    KEEPFILTERS(YEAR('Table'[date]) = year)
)
return result

Make Calendar -> Fact Table as Inactive relationship.
Slicer = Calendar,  table has only Fact table columns: