Forum Discussion
Accessible Reporting - Convert Matrix to Table, using Calculation Groups?
Hi Anonymous ,
You are absolutely right to look for a more accessible alternative - however, DAX does not support dynamic column names in a calculated table. This means we can't label columns like "July", "August", etc. based on date logic.
Instead, the best approach is to return rows with dynamic month labels and use a table visual, which is screen reader friendly.
Here’s a simplified calculated table that does this:
FlattenedClientReach =
VAR MaxMonthID = MAX('01b: Calendar'[Month Year ID])
RETURN
SELECTCOLUMNS(
ADDCOLUMNS(
GENERATE(
'AT - CLIENT REACH',
VAR Client = 'AT - CLIENT REACH'[Client ID]
RETURN
FILTER(
ADDCOLUMNS(
'01b: Calendar',
"ReachValue",
CALCULATE(
'AT - CLIENT REACH'[Measures - Client Reach],
'AT - CLIENT REACH'[Client ID] = Client
)
),
'01b: Calendar'[Month Year ID] > MaxMonthID - 12 &&
'01b: Calendar'[Month Year ID] <= MaxMonthID
)
),
"MonthName", FORMAT('01b: Calendar'[Date], "MMM YYYY")
),
"Client", [Client ID],
"Month", [MonthName],
"ClientReach", [ReachValue]
)If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Thank you.
Hi Anonymous ,
Thank you for the Response..!!
Yes, it is possible to dynamically create columns for recent months based on your Month-Year ID or a date field. You can achieve this by creating a calculated table that automatically pulls the last N months (e.g., last 12 or 24), without manually updating it each month.
Let us Assume:
You have a fact table called 'YourDataTable'
You have a related 'Date' table with a field 'MonthYearID' (e.g., 202405 for May 2024)
There’s a value column 'Value' you want to sum
1.Create a measure or variable in the calculated table that grabs the most recent MonthYearID:
VAR MaxMonthID = MAX('Date'[MonthYearID])
2.Generate dynamic calculated table
FlattenedClientReach =
VAR MaxMonthID = MAX('Date'[MonthYearID])
RETURN
ADDCOLUMNS(
'Metrics',
"M1", CALCULATE(SUM('YourDataTable'[Value]), 'Date'[MonthYearID] = MaxMonthID),
"M2", CALCULATE(SUM('YourDataTable'[Value]), 'Date'[MonthYearID] = MaxMonthID - 1),
"M3", CALCULATE(SUM('YourDataTable'[Value]), 'Date'[MonthYearID] = MaxMonthID - 2),
...
"M12", CALCULATE(SUM('YourDataTable'[Value]), 'Date'[MonthYearID] = MaxMonthID - 11)
)
This creates a wide table with 12 columns for the last 12 months.
You can name the columns "M1", "M2" etc. or use dynamic naming by formatting MonthYear text values, but this way ensures it adjusts when new data arrives.
If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!
Great, thanks v-venuppu .
Just a final piece, how do I use dynamic naming here? I'm not sure how to format the MM/YY value to text for the column header.