Dynamic column based on slicer selection

[SK87]

Hi Team,

I have Slicer for Month-Year (Column name as select month). and have created Matrix table for 12 Months where will see sum of searches of last 12months from latest month. Now I need to see dynamic column based on selection of slicer.

Incase in slicer I have selected "October 2021"; column name should appear as " 12M (Jan 21 - Oct 21) {bold part should be dynamic}

For 12Months my calculation is:

Current Year = CALCULATE(SUM('Brands Final'[Search]), PARALLELPERIOD('Brands Final'[Select Month],0,YEAR))

Previous Year =

VAR CurrentYear = PARALLELPERIOD('Brands Final'[Select Month],0,YEAR)

VAR PreviousY = SAMEPERIODLASTYEAR(CurrentYear)

RETURN

CALCULATE([Sum of Search],PreviousY)

12M = 

CALCULATE(DIVIDE([Current Year],[Previous Year])-1)

In Matrix table, In values I have added 12M KPI measure.

Please suggest best suitable way to get dynamic column.

[SK87]

Can anyone help me on this.

@SpartaBI @tamerj1 

[amitchandak]

@SK87 ,

If you choose one month and what show more than that data on axis, ou need independent date table in slicer

example measure

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -12) +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

This year

new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -1*month(_max) ) +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

Last year

This year

new measure =
var _max1 = maxx(allselected(Date1),Date1[Date])

var _max = Date(Year(_max1)-1, Month(_max1), Day(_max1))
var _min = eomonth(_max, -1*month(_max) ) +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

You can also use

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

[SK87]

Thanks for your response but I am looking for dynamic column name; calculation Part of 12Months already done at my end.

Need Name as "12M (Jan 21 - Oct 21) " on selection of slicer "October 2021

If I select July 2021 it should be "12M (Oct 20 - Jul 21)

[v-yiruan-msft · Accepted solution]

Hi @SK87 ,

If you want your field name to be displayed dynamically based on the slicer options, I'm afraid it can't be achieved... Maybe you could consider creating a measure as below and putting it on the card visual to display the field name info...

measure =
VAR _selmonth =
    SELECTEDVALUE ( 'Brands Final'[Select Month] )
RETURN
    "12M (" & [the month for 10 months ago] & " - " & _selmonth & ")"

Best Regards