Forum Discussion

mjsystemss's avatar
mjsystemss
Resolver I
4 months ago
Solved

Help Making Column Headers (Dates) Dynamic in PBIX

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

Need Help
  • vojtechsima's avatar
    vojtechsima
    4 months ago

    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.

5 Replies

  • vojtechsima's avatar
    vojtechsima
    Super User
    4 months ago

    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's avatar
    Natarajan_M
    Super User
    4 months ago

    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!

  • v-aatheeque's avatar
    v-aatheeque
    Community Support
    4 months ago

    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.