cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper III

## Dynamic column based on slicer selection

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.

1 ACCEPTED SOLUTION
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Helper III

Can anyone help me on this.

Super User

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

Helper III

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)

Community Support

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

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

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors