Dynamic Column Headers in matrix - Report Server

[MathiasChaparro]

Hi everyone:

I would like the matrix headers to be partly static and partly dynamic.

Like this below

The base structure is simple, I have a fact table, a calendar table and several dimension tables in a star schema.

For my part, try the following.

1- Create an order column in the calendar table

Orden = YEAR(CALENDARIO[FECHA]) * 100 + MONTH(CALENDARIO[FECHA])

2- Create a summary table of the fields I need to use as a slicer

TablaApoyo =
SUMMARIZE(
    CALENDARIO,
    CALENDARIO[FINMES],
    CALENDARIO[Orden],
    CALENDARIO[Mes])

3 - Create a measure that if the maximum order field in the calendar table is equal to the maximum order field in the "TablaApoyo" or equal to the maximum order value - 1, then calculate the units. In the case of the month of January, create another if condition, changing the -1 to -89 because the -89 indicates exactly the difference between January and December

PRUEBA = 
IF(
    MAX(TablaApoyo[Mes] ) = 1,
    IF(
        MAX(CALENDARIO[Orden])
            IN {MAX(TablaApoyo[Orden]) , MAX(TablaApoyo[Orden]) - 89 , MAX(TablaApoyo[Orden]) - 100},
            [Unidades],
            BLANK()
    ),
    IF(
        MAX(CALENDARIO[Orden])
            IN {MAX(TablaApoyo[Orden]) , MAX(TablaApoyo[Orden]) -1, MAX(TablaApoyo[Orden]) -100},
            [Unidades],
            BLANK()
    )
)

As a result I get the dynamic header but I cannot add the text correctly with this approach.

This is the result

I appreciate in advance any help you can give me.

Here is an example file and i based myself on this video for the solution in part, but it's not what I'm looking for. 

[MathiasChaparro · Accepted solution]

Hello Community:

I found the solution using Power Query.

I duplicate my "Calendar" table and leave only the "Fin de Mes" field, then add conditional columns to find the previous month and previous year using the functions "Date.Add.Years and Date.AddMonths"

As a next step, unpivot columns and add an index to sort the table according to my requirement.

And as a last step I created a measure to contain the values ​​I need in each column

It works perfectly and is what the user was looking for! Thank you for reading 😊

[MathiasChaparro]

I tried the example but I still need a column that has the format of the image and that is dynamic when selecting the slicer.

In the proposed solution I must create 3 new columns and it does not include the text that I must display visually as a header along with the date.

this is the final output format I'm looking for. 🙂

[lbendlin]

Calendar = ADDCOLUMNS(CALENDAR("2024-02-24",TODAY()),"Year",FORMAT([Date],"yyyy"),"Month",FORMAT([Date],"mmm"),"MNo",FORMAT([Date],"mm"),"Actual","Fecha actual: " & Format([Date],"d/mm/yyyy")
,"One Month Ago","Fecha Anterior: " & Format(EDATE([Date],-1),"d/mm/yyyy")
,"One Year ago","Ano anterior: " & Format(EDATE([Date],-12),"d/mm/yyyy"))

[MathiasChaparro]

Thank you very much for the response, would you have any example of the solution you mention?

[lbendlin]

Calendar = ADDCOLUMNS(CALENDAR("2024-02-24",TODAY()),"Year",FORMAT([Date],"yyyy"),"Month",FORMAT([Date],"mmm"),"MNo",FORMAT([Date],"mm"),"One Month Ago",EDATE([Date],-1),"One Year ago",EDATE([Date],-12))

[lbendlin]

None of these dates are dynamic. Add two (or three)  columns to your calendar table that reference "Same date last month" and "Same date last year"  in either date or text format.