Time and time again when I begin talking with Excel users and ask to see what current reports they are using, they usually show me a table with a mixed bag of columns split by different time ranges. A comparison of month over month, or year over year, mixed in with a few daily totals or cumulative totals all rolled up from values on other tabs to produce their preferred view (or dashboard). Typically, the first approach I take is to describe how we can break up this single table view of things and start looking at the aggregations of these values in easily digestible pieces and slice and dice them in different times ranges. I’ve explained that the goal should be to produce easy to consume visuals for comparison using measures and plotting these out in different ways to glean insights quicker. Most of the time, this works, but other times… it is second best to what the analyst or uber Excel user wants to see… they want to see their numbers, and they want to see them the same way they have them in Excel.
The Challenge:
Recently, I encountered this all too familiar scenario (Time Ranges in a table/matrix) except this time, I wanted to see if I could reproduce the output exactly as the end user wanted it rather than move them in a different direction.
The first group of columns showed the days in the current week, the second group showed the weeks in the current month, followed by the Months to date, a year to date column and static columns of a Goal and Forecast.
I’ll spare you the details of researching a better way than producing these as individual measures, and suffice to say that I was able to come up with a solution based on a few calculated columns, a disassociated table, and a single measure to produce the output I was searching for.
The Solution:
The above screenshot is of the dynamic matrix that you can download from the link at the end of this blog. As I developed this solution it came to my attention that there are actually a couple ways we could build this solution. The first of those would be to have a time slicer drive all the different time ranges, this would be useful for analyzing older datasets in the different ranges, but my goal was to create a solution that follows the “Set it and forget it” train of thought. This solution will restrict the view of data to never exceed the current day, the neat thing is, the current day is when you read this blog, not a static point in time. I’ve pre-loaded data out to the end of 2020, so the sample should continue to work and change each time the file is opened.
Before we dig into things, I want to convey that the DAX dove a bit deeper into the weeds than I initially expected, and I’ll do my best to describe what I did and why.
The Data
I’ve modified my original solution to use a sample of Adventure works data that I created, this simple dataset consists of a column to group things by (ModelName), a date (StartDate) and the value to aggregate (ListPrice). This solution should cover a wide range of different use cases so don’t get hung up on the exact columns here. A grouping column, a date column and a value column are all you need.
Here are the steps I took after creating the dataset and loading it into Excel:
Load Data table from Excel into Power Query, Close & Apply
Create a calculated date table (DAX):
Date =
VAR MinYear = 2018
VAR MaxYear = 2020
RETURN
ADDCOLUMNS (
FILTER (
CALENDARAUTO( ),
AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )
),
"Calendar Year", YEAR ( [Date] ),
"Month Name", FORMAT ( [Date], "mmmm" ),
"Month Number", MONTH ( [Date] ),
"Weekday", FORMAT ( [Date], "dddd" ),
"Week Number", WEEKNUM([Date]),
"Weekday number", WEEKDAY( [Date] ),
"Quarter", "Q" & TRUNC ( ( MONTH ( [Date] ) - 1 ) / 3 ) + 1
)
Your MinYear / MaxYear will obviously be different, but the core columns for what we need are in this output.
Add Calculated Columns
Now we need to add some filter columns to the date table we just created in order to get the current time frames we care about.
IsInCurrentYear = if(YEAR(NOW())= [Calendar Year],1,0)
IsInCurrentMonth = if([isInCurrentYear] && MONTH(NOW())=[Month Number],1,0)
IsInCurrentWeek = if([isInCurrentYear] && WEEKNUM(NOW())=[Week Number],1,0)
Create a Disassociated table (Dax Table)
This is our grouping table, this is the first key element in which we create a series of different DAX calculated tables to create the different time range groups we want to roll up our aggregate amount by. In each case, we are pulling all current and previous years, the current months in this year to date, the current weeks in the current month and the current days in the week. Then we union those values together where the “Group” is the top level time range, and the value is the specific time range values. Then we add an index column so that we can order the values in the way that we want.
The final output should look something like this:
This is the DAX code to create the calculated table. Each “Summarize” creates the time groups and values rolled up for the particular time range we are interested in. This is wrapped in the “AddColumns” function to add in a workable index that allows us to order all the values in the correct order dynamically. Initially, the static Index column works to sort the Group column, but the dates won’t sort as Calendar dates so I added the second way to dynamically generate an index to sort the values by. I retain the original Index value and ensure the counts returned from the date table align in sequential order. Essentially retaining the Group/Value index to sort by. Then we wrap all that in “SelectColumns” so that we can specify the column names. If we didn’t do this, the first column name would be “Calendar Year”.
TimeRange =
SELECTCOLUMNS(
UNION(
ADDCOLUMNS(
SUMMARIZE(FILTER('Date', 'Date'[Calendar Year] <= YEAR(NOW())), 'Date'[Calendar Year], "Group", "By Year", "Index", 4),
"DayIndex", CONCATENATE(4, FORMAT(CALCULATE(COUNT('Date'[Date]), ALL('Date'), FILTER('Date', 'Date'[Calendar Year]<=EARLIER('Date'[Calendar Year]))),"000"))
),
ADDCOLUMNS(
SUMMARIZE(FILTER('Date', 'Date'[Month Number] <= MONTH(NOW()) && 'Date'[Calendar Year] = YEAR(NOW())), 'Date'[Month Number], "Group", "By Month", "Index", 3),
"DayIndex", CONCATENATE(3, FORMAT(CALCULATE(COUNT('Date'[Date]), ALL('Date'), FILTER('Date', 'Date'[Month Number]<=EARLIER('Date'[Month Number]))),"000"))
),
ADDCOLUMNS(
SUMMARIZE(FILTER('Date', 'Date'[IsInCurrentMOnth] = 1 && 'Date'[Week Number] <= WEEKNUM(NOW()) && 'Date'[Calendar Year] = YEAR(NOW())), 'Date'[Week Number], "Group", "By Week", "Index", 2),
"DayIndex", CONCATENATE(2, FORMAT(CALCULATE(COUNT('Date'[Date]), ALL('Date'), FILTER('Date', 'Date'[Week Number]<=EARLIER('Date'[Week Number]))),"000"))
),
ADDCOLUMNS(
SUMMARIZE(FILTER('Date', 'Date'[IsInCurrentWeek] = 1 && 'Date'[Date] <= NOW()), 'Date'[Date], "Group", "By Day", "Index", 1),
"DayIndex", CONCATENATE(1, FORMAT(CALCULATE(COUNT('Date'[Date]), ALL('Date'), FILTER('Date', 'Date'[Date]<=EARLIER('Date'[Date]))),"000"))
),
DATATABLE("Header", STRING, "Group", STRING, "Index", INTEGER, "DayIndex", INTEGER,
{{"Goal", "Overall", 5,0}, {"Forecast", "Overall", 5,0}})),
"Value", 'Date'[Calendar Year], "Group", [Group], "Index", [Index], "DayIndex", [DayIndex]
)
Create a relationship between the Date table and the Data Table
This would be on ‘Date’[Date] and ‘Data’[StartDate]
Create our Measures
Now we need to take the grouping table and merge it with the aggregated value via our measures. In the Data table we want to create the following measures.
First Measure:
List Price = SUM(Data[ListPrice])
Second Measure:
TimeValue =
VAR Val =
SWITCH(SELECTEDVALUE('TimeRange'[Group]),
"By Year", CALCULATE(TOTALYTD([List Price], 'Date'[Date], FILTER('Date', 'Date'[Date]<= TODAY())), FILTER('Date', 'Date'[Calendar Year] = VALUE(MAX('TimeRange'[Value])))),
"By Month", CALCULATE(TOTALMTD([List Price], 'Date'[Date], FILTER('Date', 'Date'[Date]<= TODAY())), FILTER('Date', 'Date'[Month Number] = VALUE(MAX('TimeRange'[Value])) && 'Date'[Calendar Year] = YEAR(NOW()))),
"By Week", CALCULATE(SUM(Data[ListPrice]), FILTER('Date', 'Date'[Week Number] = VALUE(MAX('TimeRange'[Value])) && 'Date'[Calendar Year] = YEAR(NOW()) && 'Date'[Date]<= TODAY())),
"By Day", CALCULATE(SUM(Data[ListPrice]), FILTER('Date', 'Date'[Date] = DATEVALUE(MAX('TimeRange'[Value])))),
--Remove SWITCH below if you only want time range
SWITCH(SELECTEDVALUE(TimeRange[Value]),
"Goal", [List Price] * 1.2,
"Forecast", [List Price] * RAND()
)
)
RETURN
FORMAT(Val, "CURRENCY")
Create the Matrix
Create a Matrix visual and drop the columns into the following rows and columns:
You will have something that looks like this:
Are you ready for the magic? Head over to the far right of the visual and click down on the “Expand all down one level in the Hierarchy” button -> and BOOM!
We have our fully functional time range matrix that will adjust dynamically based on the current day. No need to update, change or alter anything! I hope you enjoyed this tip, I certainly was excited to put this solution together. There are so many different ways you could alter this solution, using different dates (swap in fiscal calendar dates), add different final total or percentage columns at the end, my mind keeps coming up with new solutions, and I hope you can use this as well!
You can find the full solution in this PBIX download which includes the sample data set.
If you like the content from PowerBI.Tips, please follow us on all the social outlets to stay up to date on all the latest features and free tutorials. Subscribe to our YouTube Channel, and follow us on Twitter where we will post all the announcements for new tutorials and content. Alternatively, you can catch us on LinkedIn (Seth) LinkedIn (Mike) where we will post all the announcements for new tutorials and content.
As always, you’ll find the coolest PowerBI.tips SWAG in our store. Check out all the fun PowerBI.tips clothing and products:
After many requests, we are now selling out layouts unbranded so that you can use them in all your business applications. Be sure to check out our first offerings and stay tuned for more to come in the future. Learn more about Layouts.
-
Layouts – MBAS 2019 Special$500.00
-
Layouts – Sunset$100.00
-
Layouts – Microsoft – Sept 2018$0.00
This DAX has some issue its not working
Date = ADDCOLUMNS(FILTER(CALENDARAUTO(),AND(YEAR([Date])>=MIN=2018,YEAR([Date])<=MAX=2020)),"Calender Year",YEAR([Date]),"month name",FORMAT([Date],"mmmm"),"Month Number",MONTH([Date]),"weekday",FORMAT([Date],"dddd"),"week number",WEEKNUM([Date]),"weekday Number",WEEKDAY([Date]),"Quarter","Q" & TRUNC((MONTH([Date] )-1) /3 )+1)
Please provide the complete details
Hi bijay, It is likely that you haven’t loaded the data yet. The CALENDARAUTO function requires a date column in a data set, so if you try to create the date table first, you’ll receive an error message that relates to that. Let me know if it is a different error, or that doesn’t work for you.
Thanks
Do have sales volume going with it?
Hi Hans-Gerlach – Depending on what you mean, you could replace the ListPrice for a volume metric. It can be whatever you want. If you mean add in another column on the end, you would replace or add in the additional column in the ListRange table (Use “Goal” or “Forecast” as an example) and add the calculation in the TimeValue measure (Use “Goal” or “Forecast” as an example again, and it should just work.
hey how to make month show the last month value? My team usually review the value of last month.
Btw, this is really helpful. Great work.
Hi Owen, that is the fun part about this solution, you can quickly modify any of the time ranges. All you need to do is open up the TimeRange Calculated table and modify the part in the second ADDCOLUMNS block that defines the month periods. instead of “<= MONTH(NOW())" you would replace that section with "= MONTH(NOW())-1" (not including double quotes). So rather than look at all previous months in this current year, we're only looking at the previous month to the one we are in. Hope that helps, glad you like it!
Thank you so much
Very nice example. This should become easier in the future with calculation groups.
Another approach could have been to have measures that show whole month values for the first day of the month, and blank() for the remaining days. (same for weeks, or years). That way you could use the date dimension as your Value columm. And if you want to limit for the last 3 months, add that filter to the measure (or to a regular slicer)
The benefits of this is you could dynamically expand or reduce the number of periods. Hmmmm thinking again you could do that with your approach too. And you approach can custom – format the column headers,which is nice, although it is more complex.
Great example nonetheless, thanks for sharing!
Great example. The DAX used in TimeRange calculation hurts my brain a little, but it is a super reference point.
One question. What is the purpose of the DayIndex Column. Apart from being used in table creation, I have been unable to find any further reference. This may well be ignorance on my past.
Many thanks.
Hi Ted, Thanks for the feedback. The DayIndex is used to sort the Value of the TimeRange table. The initial order index started to fall apart under certain time range scenarios so it forced me to add in the additional field to dynamically give me a order that I could enforce the appropriate sequence. Looking at the code again, I could probably remove the Index column, but the progression of building out and needing that dynamic range allows me to merge with the initial index values so that I retain the initial order I want while giving the dynamic nature of the data changing a method to be added to the “parent” order which I called DayIndex. There is a paragraph buried in this long article that outlines the reasoning. “Initially, the static Index column works to sort the Group column, but the dates won’t sort as Calendar dates so I added the second way to dynamically generate an index to sort the values by. I retain the original Index value and ensure the counts returned from the date table align in sequential order. Essentially retaining the Group/Value index to sort by. Then we wrap all that in “SelectColumns” so that we can specify the column names. If we didn’t do this, the first column name would be “Calendar Year”.”
Really appreciate your reply. Thanks Seth.
Ted
Hi Seth,
I have been looking at your approach to dynamic ranges using a disconnected Table. I have also looked at alternatives where the TimeRange table is linked to the Date table in a Many:1 relationship, to determine which is more efficient. In the alternate approach the KeyValue in the TimeRange table has to be a date field, any I really like your approach whereby you can specify Week Nos, Month Nos etc.
I realise that your TimeValue measure needs custom coding, depending on the Range, but I think that is an acceptable trade-off.
The only thing that I find confusing, is that when you compress the matrix to the 1st level, it is showing the values for a specific day, week or month (probably the last item based on the sort sequence). What are the implications for showing the Total Value of the Days / Weeks etc. on the compressed view?
Best Regards,
Ted
Hi Seth,
Thanks to you, I have finally got my head around this topic, and made some extensions to the model.
If you send me a contact email address, I will be happy to share the output with you.
E&OE!
Regards,
Ted
Great to hear Ted! I’ll follow up with you.
This is brilliant. Used this successfully just to show by week and by month. I do not fully understand all the DAX however.
I would like to filter out values that show zero but if I add a visual level filter for ‘greater than 0’ on the dynamic time value measure it completely changes the existing values removing a lot of them. Can anyone help explain this?