Matrix Report- Dynamic column headers

Hi janani80 

While there isn't a straightforward method for it, with a bit of creativity, it's possible.

You'll need at least two disconnected tables similar to this:

The "Disconnected_Yrs" table is just the unique values of your years in your dataset. For simplicity sake, I built it in DAX with the following expression:

Disconnected_Yrs = DISTINCT('FACT'[Year])

The "Metric" table is a table built using the "Enter Data" functionality with the metrics you want to evaluate:

Next, you'll have to create your metric calculation:

Metric_Analysis = 
// What's the current year being evaluated on the matrix visual
VAR _CurrYear = MAX('FACT'[Year])

// What is the selected year by the user?
VAR _SelectedYr = SELECTEDVALUE(Disconnected_Yrs[Year], MAX(Disconnected_Yrs[Year]))

// What is the current metric being evaluated on the matrix visual?
VAR _Metric = SELECTEDVALUE(Metric[Metric])

RETURN

// Determines which year to show which metric.
SWITCH( TRUE(),
    _Metric = "Total Sales" && _CurrYear = _SelectedYr - 1,  CALCULATE(SUM('FACT'[Total Sales]), 'FACT'[Year] = _SelectedYr - 1) ,  
    _Metric = "Outstanding Output" && _CurrYear = _SelectedYr, CALCULATE(SUM('FACT'[Outstanding Output]), 'FACT'[Year] =_SelectedYr),
    _Metric = "Units Manufactured" && _CurrYear = _SelectedYr, CALCULATE(SUM('FACT'[No of Units manufactured]), 'FACT'[Year] = _SelectedYr),
    _Metric = "Defective Pieces" && _CurrYear = _SelectedYr, CALCULATE(SUM('FACT'[Defective Pieces]), 'FACT'[Year] = _SelectedYr),
    _Metric = "Discounted Items" && _CurrYear = _SelectedYr, CALCULATE(SUM('FACT'[Discounted items]), 'FACT'[Year] = _SelectedYr)
)

All that's left is to build a matrix visual. For the simplicity of this excercise, Product and Year are from the same table. Metric is from the disconnected table and the Year slicer is also from the disconnected table. And here's the results: