In working with a customer a few weeks back, I ran into another odd scenario. This was a new ask for me, but made sense in a post COVID-19 world. Normally when I build out time intelligence with Calculation Groups, I usually only perform comparisons between the previous year and the current year. However, this customer wanted to go back a few years to be able to see the post COVID-19 impact on their metrics. Naturally, Calculation Groups makes quick work of a request such as this. However, there was no way to create their own custom headers and they found it confusing:
The use of generic headers led to confusion when reading the visualization. On top of that, they had a fiscal year that started on July 1st. This made it more complicated to understand which fiscal year they were currently in. In short, they wanted to provide dynamic column headers in the matrix visual that aligned with the associated fiscal year. If you were looking at the 2023-2024 fiscal year, the labels need to translate accordingly:
| Calculation Group Label | Custom Column Label |
| CYm3 | 2020-2021 |
| CYm2 | 2021-2022 |
| CYm1 | 2022-2023 |
| CY | 2023-2024 |
| YoY | Change from Last Year |
| YoY_Pct | % Change from Last Year |
| CYm3 Year End | 2020-2021 YE |
| CYm3 Variance YE | 2020-2021 Var YE |
| CYm2 Year End | 2021-2022 YE |
| CYm2 Variance YE | 2021-2022 Var YE |
| CYm1 Year End | 2022-2023 YE |
| CYm1 Variance YE | 2022-2023 Var YE |
Well this was a challenge and upon all of my research, I found a possible solution. And while I was able to get my solution to work, I am not 100% sure that this is still the best solution. However, it worked for my situation. And while the performance is not the greatest, it still got the job done.
To get started, you do need to understand about Calculation Groups and using Tabular Editor. If you have not used them before, the guys over at SQLBI have a great series on how to use Calculation Groups. And if you have not already done so, head over to TabularEditor.com to download the latest version to create your Calculation Groups. For our scenario today, we are going to assume you already have your Time Intelligence measures setup. Our focus is on applying the logic for the headers.
Creating the Column Headers
There are a few challenges with the creation of these column headers. One, they must map to a calculation group item. Two, they must follow the naming convention outlined above. And three, they must be properly sequenced.
We are building our logic inside of Power Query under the assumption we do not have access to our data source to enact this solution. This allows us to see how versatile Power Query can be for our projects. However, you are welcome to perform this logic upstream in a SQL server if you have one available.
If you are not comfortable with Power Query, I suggest you check out my book which will give you the knowledge and experience to create this table.
Reference your date table
The first step in the process is that we will right click on our date table and reference it in another query. From there, we will right click on the Fiscal Year column and remove all other columns. Once completed, we will remove the duplicates in the fiscal year column so our results look like this:4
This is the easiest way to ensure that your custom column headers will align with your current date table. This will eliminate any issues with it being out of sync in the future.
Add mappings for Calculation Groups
Our next step is to add our mappings so our custom columns will leverage the right calculation group measures. Referencing the table above, we will add a new column for each calculation group item. We will also fill in the column with the sequence value in the order we wish to display these items.
For each measure, go to the Add Columns ribbon and select Custom Column. Change the column name from “Custom” to your label from the Calculation Group. Then add the sequence number in the formula box below:
When finished, your list of labels should look something like this:
The last thing we need to do is unpivot our columns so they become rows in the dataset. Right click on the Fiscal Year column and select “Unpivot Other Columns”. This will turn the other columns into rows. Rename the columns so they are measure and measure sequence respectively:
Now that this has been done, it is time to build out our logic for the labels.
Assigning labels with a conditional column
At this point, you have two choices. Because of the complex nature of our labels, you will need to write them out in the advanced editor. The question is if you would prefer to get the conditional column logic in place first. If you are not comfortable with the logic, you can use the graphical interface to achieve this. Otherwise, you can use if/then/else in the advanced editor.
I am not going to get into the weeds with this code as there were a number of functions used. However, I will provide a full printout of the code for this query when we are finished. In the end, our goal is to have our labels show up accordingly:
This process will take some time, but the trick is to use the measure sequence to make your life easier. By using that, it will make your creation of column headers easier. It takes some practice, but you will get there quickly.
Address sequence of columns
This was the trickiest part. We will implement this solution using conditional columns, but there is a catch. We will be sorting our custom header column with this new sequence column. However, we must have an equally distinct value return for the sequence. So how do we make that work?
The trick is grouping. CY, CYm1, CYm2, and CYm3 will be part of the first group. YoY will be in group 2 and group 3 will be YoY_Pct. Then the year end and year end variance columns will be part of group four.
Now, you might already be concerned about a collision. And you are correct, there is one at this moment. But we are going to do one more thing to fix this. For groups 1 and four, we are going to add the fiscal year as a decimal value. This will allow us to use consistent headers and maintain sequencing. I promise you that I have spent hours figuring this out and I do not know of a better way to achieve this outcome. If you think there is a better way to do it, give it a try. But I know for a fact that this outcome works:
Now that we have this in place, we can look at the final code.
Final Code – Custom Column Headers
As you can tell, this was not an easy process. It took a lot of custom coding to make it work. However, the results will speak for themselves shortly. Regardless, here is the code we used to achieve our outcome:
let
Source = bi_date,
#"Removed Other Columns" = Table.SelectColumns(
Source,
{"FiscalYear"}
),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
#"Added CY - 3" = Table.AddColumn(#"Removed Duplicates", "CYm3", each 0, Int64.Type),
#"Added CY - 2" = Table.AddColumn(#"Added CY - 3", "CYm2", each 1, Int64.Type),
#"Added CY - 1" = Table.AddColumn(#"Added CY - 2", "CYm1", each 2, Int64.Type),
#"Added CY" = Table.AddColumn(#"Added CY - 1", "CY", each 3, Int64.Type),
#"Added YoY" = Table.AddColumn(#"Added CY", "YoY", each 4, Int64.Type),
#"Added YoY%" = Table.AddColumn(#"Added YoY", "YoY_Pct", each 5, Int64.Type),
#"Added CY - 3 YE" = Table.AddColumn(#"Added YoY%", "CYm3 Year End", each 6, Int64.Type),
#"Added CY - 3 Var YE" = Table.AddColumn(
#"Added CY - 3 YE",
"CYm3 Variance YE",
each 7,
Int64.Type
),
#"Added CY - 2 YE" = Table.AddColumn(
#"Added CY - 3 Var YE",
"CYm2 Year End",
each 8,
Int64.Type
),
#"Added CY - 2 Var YE" = Table.AddColumn(
#"Added CY - 2 YE",
"CYm2 Variance YE",
each 9,
Int64.Type
),
#"Added CY - 1 YE" = Table.AddColumn(
#"Added CY - 2 Var YE",
"CYm1 Year End",
each 10,
Int64.Type
),
#"Added CY - 1 Var YE" = Table.AddColumn(
#"Added CY - 1 YE",
"CYm1 Variance YE",
each 11,
Int64.Type
),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(
#"Added CY - 1 Var YE",
{"FiscalYear"},
"Measure",
"MeasureSequence"
),
#"Added ColumnHeaders" = Table.AddColumn(
#"Unpivoted Columns",
"ColumnHeaders",
each
if [MeasureSequence] = 0 then
Number.ToText([FiscalYear] - 3)
& "-"
& Number.ToText([FiscalYear] - 2)
else if [MeasureSequence] = 1 then
Number.ToText([FiscalYear] - 2)
& "-"
& Number.ToText([FiscalYear] - 1)
else if [MeasureSequence] = 2 then
Number.ToText([FiscalYear] - 1)
& "-"
& Number.ToText([FiscalYear])
else if [MeasureSequence] = 3 then
Number.ToText([FiscalYear])
& "-"
& Number.ToText([FiscalYear] + 1)
else if [MeasureSequence] = 4 then
"Change from Last Year"
else if [MeasureSequence] = 5 then
"% Change from Last Year"
else if [MeasureSequence] = 6 then
Number.ToText([FiscalYear] - 3)
& "-"
& Number.ToText([FiscalYear] - 2)
& " YE"
else if [MeasureSequence] = 7 then
Number.ToText([FiscalYear] - 3)
& "-"
& Number.ToText([FiscalYear] - 2)
& " Var YE"
else if [MeasureSequence] = 8 then
Number.ToText([FiscalYear] - 2)
& "-"
& Number.ToText([FiscalYear] - 1)
& " YE"
else if [MeasureSequence] = 9 then
Number.ToText([FiscalYear] - 2)
& "-"
& Number.ToText([FiscalYear] - 1)
& " Var YE"
else if [MeasureSequence] = 10 then
Number.ToText([FiscalYear] - 1)
& "-"
& Number.ToText([FiscalYear])
& " YE"
else if [MeasureSequence] = 11 then
Number.ToText([FiscalYear] - 1)
& "-"
& Number.ToText([FiscalYear])
& " Var YE"
else
"Error",
type text
),
#"Added HeaderSequence" = Table.AddColumn(
#"Added ColumnHeaders",
"HeaderSequence",
each
if [MeasureSequence] = 0 then
(([FiscalYear] - 3) / 10000) + 1
else if [MeasureSequence] = 1 then
(([FiscalYear] - 2) / 10000) + 1
else if [MeasureSequence] = 2 then
(([FiscalYear] - 1) / 10000) + 1
else if [MeasureSequence] = 3 then
(([FiscalYear]) / 10000) + 1
else if [MeasureSequence] = 4 then
2
else if [MeasureSequence] = 5 then
3
else if [MeasureSequence] = 6 then
(([FiscalYear] - 3) / 10000) + 4
else if [MeasureSequence] = 7 then
(([FiscalYear] - 3) / 10000) + 4.00001
else if [MeasureSequence] = 8 then
(([FiscalYear] - 2) / 10000) + 4
else if [MeasureSequence] = 9 then
(([FiscalYear] - 2) / 10000) + 4.00001
else if [MeasureSequence] = 10 then
(([FiscalYear] - 1) / 10000) + 4
else if [MeasureSequence] = 11 then
(([FiscalYear] - 1) / 10000) + 4.00001
else
0,
type number
)
in
#"Added HeaderSequence"It almost looks worse than you might imagine. However, keep in mind that some of this is to help ensure we have a solid experience with our custom headers.
At this point, we will close and apply our query changes and load them into our data model.
Preparing the data model
At this point, we have created our custom headers. Next, we must map them for use with our Calculation Groups and fit them into our model. We will need to build a relationship to our custom headers query from our date table and create a custom measure to leverage our calculation group.
Connect date and custom headers table
Our first task is to connect our date table with our custom column headers table. This will filter down our custom column to only show the headers we need for a selected fiscal year. We will simply connect the fiscal year columns on both tables to build the relationship.
Now, you might be concerned about the many to many relationship being established. While often discouraged, there are times where they are required. This is one of those moments. We will go ahead and click OK to create the relationship:
With this relationship in place, we can now focus on creating a measure to return our desired results!
Custom measure for Calculation Groups
The last step before we create our visualization is to create a measure that leverages our calculation groups, but it is performed from the custom column headers table. A fun fact that you might not realize is that you can use your calculation groups as a filter in a CALCULATE() function. We are going to use the measures we created inside of the custom column headers table to perform that exact filtering!
The formula is simple. We are going to use CALCULATE(), call our Revenue measure, and select our time intelligence measure to apply. The result is a measure that looks like this:
Total Revenue =
CALCULATE (
[Revenue],
TimeIntelligence[Name] = MAX ( CustomColumnHeaders[Measure] )
)With our measures set, we just need to create our matrix.
Add custom headers to our matrix
To complete the process, we need to simply update our matrix. For the column headers, replace the time intelligence field with the custom headers from our custom column headers table. We then need to replace our measure with our new custom measure to ensure the data comes back correctly. The result is the following:
This is quite the process to setup, but it can be rewarding if it works out. It will stretch your skills in a few areas within Power BI. However, the results speak for themselves.
Anything else to consider?
Keep in mind that if you go this route, updating the logic with your calculation groups will grow increasingly complex. If you need to add a new measure, this will add an additional burden to the update process. Each new item will need to be updated manually in Power Query resulting in additional effort.
My recommendation is that you take the time upfront to establish all of the requirements. This will help avoid future rework in the future.
Conclusion
First of all, you read all of this and understood it – congratulations! This is a very complex topic and not easy to digest. You might need to re-read this article a few times to fully comprehend what happened, but I promise it will work out. What is important is that you have a grasp of the key elements.
So have you ever ran into a requirement like this? Do you have a more efficient way to achieve this outcome? Do you plan on using something like this in the future? Tell me about it in the comments below!
Leave a Reply
You must be logged in to post a comment.