Matrix showing metric names as rows, months as columns and actual values as values

[AndyDD_UK]

I have a Matrix showing metric names as rows and months as columns. I want to show values for Jan of selected year to selected month. eg if user selected April, the matrix would show Jan - April, if they selected May, would show Jan - May and so on.

To achieve this I have:

1) a year slicer which interacts with the matrix

2) a single select month slicer which doesn't interact with the matrix...otherwise the matrix would only show one month. NB: there is a good reason for making it single select.

The best I can get to is using startdate and enddate based on SelectedValue of Year and SelectedValue of Month but this shows all months of the year.

Your help very much appreciated

Model:

I can't post model but here are key components:

1) Date calendar connected to Fact table by date

2) Fact table contains metric ID connected to a Dim Metric table, the metric names are used in the matrix

3) Fact table containing [ActualValue] which is the value I'm trying to show in the matrix for each metric name

[danextian · Accepted solution]

Hi @AndyDD_UK 

You will need a disconnected table to be able to show rows beyond what is selected otherwise you'll be limited to thee current selection.

Month Name in the screenshot below is from a disconnected table which also has a column for month numbers. Year column needs to be from the related dates table.

Please see the attached sample pbix.

[AndyDD_UK]

Thanks so much, got it to work in mine!

[FarhanJeelani]

Hi @AndyDD_UK ,

Ensure Your Date Table is Configured:

Your date table should include columns for Year, Month Name, and Month Number.

Ensure these are properly set up and marked as a date table.

• Create a Measure for the Matrix: Use the following DAX measure to filter the data:

  DAX

  ShowValues =

  VAR SelectedYear = SELECTEDVALUE('Date'[Year]) VAR SelectedMonth = SELECTEDVALUE('Date'[Month Number]) VAR CurrentMonth = MONTH('Date'[Date]) VAR CurrentYear = YEAR('Date'[Date]) RETURN IF( CurrentYear = SelectedYear && CurrentMonth <= SelectedMonth, [ActualValue], -- Replace with your actual value column BLANK() )

• Modify Interactions:

  • Year Slicer: Ensure the year slicer interacts with the matrix.
  • Month Slicer: Disable interaction between the month slicer and the matrix to prevent it from directly filtering the displayed months.

• Set Up the Matrix:

  • Rows: Use the Metric Name column from your Dim Metric table.
  • Columns: Use the Month Name column from your date table.
  • Values: Use the ShowValues measure.

• Sort the Months: Ensure the Month Name column is sorted by Month Number in your date table to display months in chronological order.

Please mark this as solution if it helps you. Appreciate Kudos.

[AndyDD_UK]

Thanks for this, though I couldn't get it to work. Used solution above. Thanks again.