Forum Discussion

SK87's avatar
SK87
Helper III
4 years ago
Solved

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 s...
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

amitchandak's avatar
amitchandak
Super User
4 years ago

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's avatar
    SK87
    Helper III
    4 years ago

    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)

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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