Help Making Column Headers (Dates) Dynamic in PBIX

[mjsystemss]

Hello,

I am working on a Power BI Desktop Report Matrix with One Row and 4 Fields in Values Namely
Activity          Lastweek      Weekly_Activity          Today        Percentage(%)                   Total_Amount.

I want Lastweek and Today to show dynamic dates when the report runs. I have tried using card to cover or place on top of the dates but it is not stable when published to Power BI Service.

How can Resolve this.

Thanks

[v-aatheeque]

Hi @mjsystemss 

Have you had a chance to look through the responses shared earlier? If anything is still unclear, we'll be happy to provide additional support.

[vojtechsima · Accepted solution]

Hey, @mjsystemss ; you can do this nicely with field parameters with no additional setup (except the field parameters).

Result would look like this:

First, create the measures you need:

Then create a Field Parameter:

Give it a name and put the measures there (just put anything really, you can modify it later):

Then go to Data pane; check out the new Field Parameter table and modify if it like this:

Adjusted order and replace the Text string of the names of Today/LastWeek measures as I have.

Dynamic_Matrix_Headers = {
    (FORMAT(TODAY() - 7, "dd-MM-yyyy"), NAMEOF('Fact_Activity'[Measure_Lastweek]), 0),
    ("Weekly_Activity", NAMEOF('Fact_Activity'[Measure_WeeklyActivity]), 1),
    (FORMAT(TODAY(), "dd-MM-yyyy"), NAMEOF('Fact_Activity'[Measure_Today]), 2),
    ("Percentage(%)", NAMEOF('Fact_Activity'[Measure_Percentage]), 3),
    ("Total_Amount", NAMEOF('Fact_Activity'[Measure_TotalAmount]), 4)
}

Then put the Field Parameter's column to your Matrix:

The name will probably be different, but it doesn't matter.

Then you'll get this:

Attaching PBI, so you can test it yourself.

[Natarajan_M]

Hi @mjsystemss , From your query, I understand that you need dynamic column names in the table. Is my understanding correct?

I tried to recreate the scenario using the sample data , check whetehr the below approach works for you 


Sample Input :

Header table :

Sample OP :

MatrixValue = 
SWITCH(
    SELECTEDVALUE(MetricHeaders[MetricKey]),
    "LastWeek",
        CALCULATE(SUM(ActivityData[Amount]),
            ActivityData[Date] = TODAY() - 7),
    "Weekly_Activity",
        CALCULATE(SUM(ActivityData[Amount]),
            ActivityData[Date] >= TODAY() - 7 &&
            ActivityData[Date] <= TODAY()),
    "Today",
        CALCULATE(SUM(ActivityData[Amount]),
            ActivityData[Date] = TODAY()),
    "Percentage",
        DIVIDE(
            CALCULATE(SUM(ActivityData[Amount]), ActivityData[Date] = TODAY()),
            SUM(ActivityData[Amount]), 0),
    "Total_Amount",
        SUM(ActivityData[Amount]),
    BLANK()
)

Header table :

MetricHeaders = 
DATATABLE(
    "SortOrder", INTEGER,
    "MetricKey", STRING,
    {
        {1, "LastWeek"},
        {2, "Weekly_Activity"},
        {3, "Today"},
        {4, "Percentage"},
        {5, "Total_Amount"}
    }
)

Calculated column :

MetricLabel = 
SWITCH(
    MetricHeaders[MetricKey],
    "LastWeek",        "Last Week (" & FORMAT(TODAY()-7, "MMM DD, YYYY") & ")",
    "Weekly_Activity", "Weekly Activity",
    "Today",           "Today (" & FORMAT(TODAY(), "MMM DD, YYYY") & ")",
    "Percentage",      "Percentage (%)",
    "Total_Amount",    "Total Amount",
    MetricHeaders[MetricKey]
)

Pbix :
Today_Weekly.pbix

Thanks 
If you found this helpful, please consider giving it a kudo and marking it as the accepted solution — it goes a long way in helping others facing the same issue.

For more Power BI tips and discussions, let’s connect on LinkedIn:
https://www.linkedin.com/in/natarajan-manivasagan

Cheers!

[FBergamaschi]

Hi @mjsystemss 

can you please show images? That will explain better what you are looking for, which is unclear to me

Thanks

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[mjsystemss]

I want headers encircled to display as dates