I find I do love a good challenge and I find that I get them frequently. I also enjoy sharing what I have learnt with others.

In this blog post I am going to show you how I found a way to create Dynamic Date Column Headers for Tables or Matrixes in Power BI.

Below is what it looks like once completed. What I did was to create a matrix with the last 5 Days (Showing each date for the last 5 days) and then along with this an MTD calculation which shows not only the header of MTD but it includes the current months values.

Creation of Period Table

I have previously blogged on how to create and map through the period table. If you can please complete the steps from my linked blog post before completing the additional steps.

Here is a link to this blog post: Create Dynamic Periods for Fiscal or Calendar Dates in Power BI

Creating the last 5 Days dynamic header with the period table

In the steps below I will show you how I created the Last 5 days in the period table.

This is where the magic happens. The reason for this is because as each day moves along it will automatically update the values. The result of this is that there is no need to manually update or change values.

Not only that but because of the configuration of the Period table, the data will automatically add up based on the period.

  • NOTE: In my period table I have already created a variable which is called TodaysDate
    • This contains the MAX Date in my dataset.
  • This is a pattern that will be repeated for each of the last 5 days. I will go through it in detail for the first date.
    • The rest of the days will just go back one day in time.
  • I went into my Period table, and then went into the Advanced Editor
  • The requirement for the Period Function requires the following:
    • Period (This is the name for the Period)
    • Date (This is the date for the associated period, which contains the start date and end date)
    • Sort (This will allow for the sorting of the period as required)
    • PeriodType (This is what type of period it is)
  • Below is what the code is to create the Max Date or Date -0

    // Max Date

    {Date.ToText((TodaysDate),”dd”) & “-” & Date.ToText(TodaysDate,”MMM”) & “-” & Date.ToText(TodaysDate,”yyyy”),

    TodaysDate,

    TodaysDate,

    36,

    “Last 5 Days”},

    • Line 1 – This is a comment
    • Line 2 – This is the Period Name
      • What I am doing here is I am dynamically creating the Period Name using the Power Query Functions to dynamically create the name.
      • For my requirement I want to display the actual date as the Period
      • EG: 25-Aug-2020
    • Line 3 – This is the start date for my period
      • For this example, it would be the last/max date which is TodaysDate
    • Line 4 – This is the end date for my period
      • For this example, it would be the last/max date which is TodaysDate
    • Line 5 – This is the Sort number, which will be the largest number in my Last 5 days, this is so that it will always be last.
    • Line 6 – This is the PeriodType and in my example it is called Last 5 Days
      • The bonus with the PeriodType is that it allows me to filter the matrix to always show the last 5 days, and whilst the data will change, the filtering will not change because I am using the PeriodType.
  • This is what it looks like once run in the Period Table
  • I then repeat this process for the previous days where I go back one Date with the code below.

    // Max Date -1

    {Date.ToText((Date.AddDays(TodaysDate,+1)),”dd”) & “-” & Date.ToText(Date.AddDays(TodaysDate,-1),”MMM”) & “-” & Date.ToText(Date.AddDays(TodaysDate,-1),”yyyy”),

    Date.AddDays(TodaysDate,-1),

    Date.AddDays(TodaysDate,-1),

    35,

    “Last 5 Days”},

    • I would then repeat this for each date going back each day by increasing by -1
    • EG: For going back to days it would be -2
  • This is what it looks like in my period table for the Last 5 days

  • Once done I would then load all the data into the data model.

Creating the Matrix with the Dynamic Headers for the Last 5 Days

Below is how I created the Dynamic Headers

  • I created the matrix with the following below
    • I put in the column called “Period” from the ‘Period’ table into the columns
    • I then put my measure [Sales] into the Values.
  • Then I took the PeriodType from the ‘Period’ table and put it into the Filters on this visual.
    • I then filtered it to “Last 5 Days”
  • I could then see the Matrix table, as well as the validation of the table below to make sure that the values do add up correctly.

Creating the MTD (Month to Date) dynamic header with the period table

In the steps below I will show you how I created the Month to Date in the period table.

  • Below is what the code is to create the Current MTD
    • My requirement was for the MTD to have the header in the format of “MTD (01-25 Aug)”

      /////////////////////////////////////////////

      // Current MTD

      /////////////////////////////////////////////

      { “MTD (” & Date.ToText(Date.StartOfMonth(TodaysDate),”dd”) & “-” & Date.ToText(TodaysDate,”dd”) & ” ” & Date.ToText(TodaysDate,”MMM”) &”)” ,

      Date.StartOfMonth(TodaysDate),

      TodaysDate,

      1,

      “Current MTD”},

    • Line 1 – 3 – This is a comment
    • Line 2 – This is the Period Name
      • What I am doing here is I am dynamically creating the Period Name using the Power Query Functions to dynamically create the name.
      • For my requirement I want to display the Month to date as the Period
      • EG: “MTD (01-25 Aug)”
    • Line 3 – This is the start date for my period
      • For this example, it would be the start of the current month
    • Line 4 – This is the end date for my period
      • For this example, it would be the last/max date for the current month
    • Line 5 – This is the Sort number, which will be the largest number in my Last 5 days, this is so that it will always be last.
    • Line 6 – This is the PeriodType and in my example it is called “Current MTD”
      • The bonus with the PeriodType is that it allows me to filter the matrix to always show the last 5 days, and whilst the data will change, the filtering will not change because I am using the PeriodType.
  • This is what it looks like once run in the Period Table
  • And this is what it looks like once loaded below
    • NOTE: IN here I have got a combination of the “Last 5 Days” and “Current MTD” within one Matrix. HOW COOL IS THAT?


  • As shown above this is showing the correct data and it will change as each and every day the dataset is updated.

Summary

In this blog post I have shown how to create the values required for the dynamic column headers. And then how to integrate this into my current reporting using a matrix.

Here is a link to the associated PBIX where you can view the code on how I got this created: BLOG – Dynamic Matrix Column Headers.pbix

Thanks for reading and I hope that you found this blog post useful. I really enjoyed getting this working and I hope it can help others! All comments are welcome in the section below.