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 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.

  • 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

4 Replies

  • 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