Forum Discussion

MathiasChaparro's avatar
MathiasChaparro
Frequent Visitor
2 years ago
Solved

Dynamic Column Headers in matrix - Report Server

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 ...
header
matrix
ReportServer
  • MathiasChaparro's avatar
    MathiasChaparro
    2 years ago

    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's avatar
MathiasChaparro
Frequent Visitor
2 years ago

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's avatar
    lbendlin
    Super User
    2 years ago
    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"))