Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
SK87
Helper III
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

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.

View solution in original post

4 REPLIES 4
SK87
Helper III
Helper III

Can anyone help me on this.

 

@SpartaBI @tamerj1 

amitchandak
Super User
Super User

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

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)

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors