How far back does your sales data go? What granularity do you want to see when looking at your data? If you are like most, you probably have a lot of data, and the granularity you want to see will depend on the context of the question being asked. You could use the drill down and drill up features to change the granularity, but this can be cumbersome or not intuitive for the end user.
In this blog, we will show you an alternative that allows you to make the granularity dynamic depending on how many days of data you view. Don’t worry if you don’t have a lot of data to look at right now. You will soon, and this handy trick will also work for you.
What is a Dynamic Date Axis, and When Should You Use One?
A dynamic date axis is one where the granularity changes depending on the number of days worth of data viewed. It is very useful when you have a lot of data, and you want to view it in a proper context. For example, if you are viewing three years’ worth of sales data, do you want to see it daily, or would monthly or yearly be more appropriate for your purposes?
A dynamic date axis can be used anytime you show data over a date range. The amount of data you have is not essential, but if you set up the dynamic date axis from the beginning, it will work as your data grows. This will allow you to set the axis and forget if you want versus updating your visuals as the data grows with your company.
How to Create a Dynamic Date Axis
Creating your dynamic date axis, as described in this blog, requires a date table in your dataset and two additional tables that will be created with DAX. It also requires one measure. We will go over all of the created pieces in this section. This first table will be created based on whichever date you want to be dynamic and set the granularity. For this example, we will use some data from the Superstore public dataset and work with the order date.
Our table is a union of four smaller tables, each having four columns: the date, a “visual” date (the date that will show on the graphic), a granularity (daily, monthly, yearly, or dynamic), and order (for ordering the type). This table should have a two-way relationship between the date in the date table and the date you want to use a dynamic axis with (Order date). The code is shown below.
Dynamic Dates =
UNION(
ADDCOLUMNS(
CALENDAR(min(Orders[Order Date]),max(Orders[Order Date])),
"Viz Date", [Date],
"Granularity", "Daily",
"Order",1
),
ADDCOLUMNS(
CALENDAR(min(Orders[Order Date]),max(Orders[Order Date])),
Viz Date, DATE(YEAR([Date]), MONTH([Date]),1),
"Granularity", "Monthly",
"Order",2
),
ADDCOLUMNS(
CALENDAR(min(Orders[Order Date]),max(Orders[Order Date])),
"Viz Date", DATE(YEAR([Date]),1,1),
"Granularity", "Yearly",
"Order",3
),
ADDCOLUMNS(
CALENDAR(min(Orders[Order Date]),max(Orders[Order Date])),
"Viz Date", [Date],
"Granularity", "Dynamic",
"Order",4
)
)
The calculation starts with UNION, which will append each table as it is created to the bottom of the previous or larger table. All four tables must have the same set of columns for this to work correctly.
The ADDCOLUMNS function allows us to build each of the four tables exactly how we wish, using a combination of calculated and specified columns and values. The first thing in each ADDCOLUMNS is a calculated column providing a calendar of dates between the minimum and maximum order date from the Orders table.
The following line starts a new column, “Viz Date,” with a calculated value for each row. The third line adds the “Granularity” field, which is specified for every row in the table.
The last line also specifies the “Order” field for each row. The fourth table is for the dynamic axis, and though it has a date at each point, those dates won’t be used when this granularity is selected.
The following table we create will be used for selecting the granularity and includes only two columns: the “Granularity” and “Order” from the previously created table. The code is below.
Granularity selections =
ADDCOLUMNS(
values('Dynamic Dates'[Granularity]),
"Order", LOOKUPVALUE('Dynamic Dates'[Order],'Dynamic Dates'[Granularity],'Dynamic Dates'[Granularity])
)
The final piece to the puzzle is the measure we will need in order to know which level of granularity will be shown. The code is below.
m_date_filter =
VAR slicer_selection = SELECTEDVALUE('Granularity selections'[Granularity])
VAR current_type = SELECTEDVALUE('Dynamic Dates'[Granularity])
VAR num_days = DATEDIFF(min(Orders[Order Date]),max(Orders[Order Date]),DAY)
RETURN
SWITCH(
true(),
slicer_selection = "Daily" && current_type = "Daily",1,
slicer_selection = "Monthly" && current_type = "Monthly",1,
slicer_selection = "Yearly" && current_type = "Yearly",1,
slicer_selection = "Dynamic" && num_days <= 90 && current_type = "Daily",1,
slicer_selection = "Dynamic" && num_days > 90 && num_days <=730 && current_type = "Monthly",1,
slicer_selection = "Dynamic" && num_days > 731 && current_type = "Yearly",1,
0
)
The first part of this measure is three variables.
slicer_selectionfinds the filtered “Granularity” value of the second table created.current_typefinds the value of “Granularity” from the first table created.num_dayscalculates the total days between the minimum and maximum dates.
The second variable is difficult to understand because we won’t be physically selecting the type from the first table. The measure evaluates every row of the “Dynamic Dates” table to determine the granularity in that row. That row’s granularity is what is evaluated as the selected value.
When evaluating the measure for return, it might be easiest to think of it this way. If “Daily,” “Monthly,” or “Yearly” are chosen from the Granularity selections table’s granularity filter, every row with a matching “Granularity” in the “Dynamic Dates” table would be assigned a value of 1, and all others a value of 0.
If “Dynamic” is chosen and the date difference is less than 90 days, every row with a “Granularity” of “Daily” in the “Dynamic Dates” table will be assigned a value of 1 and all others a value of 0. “Monthly” granularities are given a value of 1 on a date difference between 91 and 730 days, and “Yearly” gets a value of 1 for any date differences over 730 days.
It may seem like the measure is recursive, but it isn’t when you look at it on a row-by-row basis. For one selection, we are making an actual row selection. For the other, we evaluate the row and determine the selection based on the field contents.
This may be more easily explained with pictures.
In the above image, a filter has not been applied to the “Granularity selection” table, left figure. Because of this, the measure shows 0 for all types in the “Dynamic Dates” table. We also have no filtered dates.
Above, we see that a filter has been applied to the “Granularity selections” table, and the measure has returned one where the type of the “Dynamic Dates” table is equal to “Daily.” Notice the date range is vast but is ignored due to the first selection.
“Monthly”.
“Yearly”.
Let’s look at what happens when “Dynamic” is selected.
Above, we see that “Dynamic” is selected, and we have a wide date range. Because our measure is set up to use “Yearly” any time the date difference is over 730 days, all rows in the “Dynamic Dates” table that have “Granularity” equal to “Yearly” would have a measured value of one. The following two images will show “Dynamic” for “Monthly” and “Daily.”
Great! We have two new tables and a measure. It doesn’t do us much good until we apply it to a visual. So, let’s build a two-line chart. These are useful for visualizing data over time and lend themselves well to a dynamic date axis.
On the X-axis, we will put “Viz Date” from the “Dynamic Dates” table we created. On the Y-axis, we will put “sales” and “COGS” (which is a calculated column of “sales” – “profit”). The secondary Y-axis was not chosen as the two fields are of the same units and scale.
Now add the “Granularity” from the “Dynamic Dates” table to the filters pane for this visual, and set it to Top N. Set n = 1 and use the measure we created as the “By value.” Remember how the measure evaluated each “Dynamic Dates” row and assigned a 1 or 0? This is where that comes in; only the type with a one will have its “Viz Date” used for summing the data.We will add a date slicer to the report using the date from the date table and a slicer for our “Granularity” from the “Granularity selections” table. Other slicers can be added as you wish, and in the example report, we will see a “Region” slicer is included.
The below image shows the chart with the “Granularity” slicer set to “Daily.” As you can see, the chart is difficult to read.
The following image shows the same date range but uses the “Dynamic” granularity.
Finally, a reduced date range that shows the data on a “Monthly” basis when “Dynamic” is selected.
Extras
An additional type of “Quarterly” can be added, but the code for doing so is a little more complex than intended for this blog. Using tiles or individual year and month filters is possible with this trick, but choosing a month without a year also being selected will lead to unintended results. You can make this “set it and forget it” by hiding the granularity slicer using View>Selections or leaving it shown for the user to use. I like to hide it when I don’t want an option to force “Daily,” “Monthly,” or “Yearly” available. The size of the date differences used to change from “Daily” to “Monthly” to “Yearly” when using “Dynamic” is completely up to you. I like to look at it from a monthly perspective. I would only want to see 2-3 months of data on a daily view, 3-24 or 36 months on a monthly view, and anything bigger on a yearly view. Play around with this and use what works best for you.
Closing
A dynamic date axis is useful when you want the granularity to automatically change with an increasing or decreasing number of days to be evaluated. By creating the tables and measurements from this blog, users can still select granularity without having to use the drill-up and drill-down functions on the visual.
If you need additional help or are curious about how to leverage Power BI better, reach out to the experts at phData today for help, guidance, and best practices!
FAQs
You can limit this however you want, and as mentioned, you can add quarterly if you feel like it.
Yes, you can use this on multiple visuals within the same report. The key is to use the viz dates as the axis and to add the granularity filter with Top1.
