topic Dynamic column based on slicer selection in DAX Commands and Tips

Dynamic column based on slicer selection

SK87 — Tue, 12 Jul 2022 07:01:26 GMT

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.

Re: Dynamic column based on slicer selection

amitchandak — Tue, 12 Jul 2022 08:02:25 GMT

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]))

Re: Dynamic column based on slicer selection

SK87 — Tue, 12 Jul 2022 08:56:59 GMT

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)

Re: Dynamic column based on slicer selection

SK87 — Wed, 13 Jul 2022 06:46:39 GMT

Can anyone help me on this.

SpartaBI tamerj1

Re: Dynamic column based on slicer selection

Anonymous — Tue, 19 Jul 2022 04:49:02 GMT

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