Dynamic Column names in matrix

DAX
1

Dears, looking for a good solution either by Measures or Calculation groups to have a Dynamic Column name in matrix based on user selection as per these requirements:
if user selected Year 2025 and Month Feb, 4 columns should appear:
Jan 2025, Feb 2025, VAR, VAR%
and if user selected Jan, then columns should be:
Dec 2024, Jan 2025, VAR, VAR%

I’m sure someone has solved that limitation
thanks in advance

2

Hi @mahmoud.algindy - Can you clarify the requirement, basically you want to show data for Selected and prior month along with VAR, VARS%.

Is it correct ?

Thanks
Ankit J

3

Thank you @ankit

Yes, but instead to show the columns with static names like Current Month, Prev. Month instead show:
Jan 2025 , Feb 2025 , VAR, VAR%

4

Hi @mahmoud.algindy - This is not straightforward but can use below workaround as shown with sample Sales and Dates data in attached file.

  1. Created a separate Disconnected table from Date table to act as Source for the Matrix assuming slicer is combination of Month & Year. Adding fields for Var and Var_Perc.

Matrix Table = UNION(all(Dates[Month & Year],Dates[MonthnYear]),row(“Month & Year”,“Var”,“MonthnYear”,100000000),row(“Month & Year”,“Var%”,“MonthnYear”,100000001))

  1. Created a measure to get data for Current and Prev month along with Var and Percentage.
Final Measure = var CurrMonth = max(Dates[Month & Year])

var PrevDate = STARTOFMONTH(Dates[Date]) - 1

var PrevMonth = CALCULATE(max(Dates[Month & Year]),Dates[Date] = PrevDate)

var MatrixMonth = SELECTEDVALUE('Matrix Table'[Month & Year])

var CurrSales = CALCULATE([Total Sales],filter(all(dates),Dates[Month & Year] = CurrMonth))

var PrevSales = CALCULATE([Total Sales],filter(all(dates),Dates[Month & Year] = PrevMonth))

var Var1 = CALCULATE([Total Sales],filter(all(dates),Dates[Month & Year] = CurrMonth)) - CALCULATE([Total Sales],filter(all(dates),Dates[Month & Year] = PrevMonth))

var Var_Per = format(DIVIDE(var1,CALCULATE([Total Sales],filter(all(dates),Dates[Month & Year] = CurrMonth))),"percent")

return

switch(MatrixMonth,CurrMonth,CurrSales,PrevMonth,PrevSales,"var",Var1,"Var%",Var_Per,BLANK())

For more details, refer attached sample PBIX file.

EDNA_DynamicNames.pbix (349.2 KB)

Thanks
Ankit J

2 Likes
5

Thanks a lot @ankit
This is great workaround.