Updated January 2026. Originally published April 2020.
Time intelligence in Power BI (business intelligence) can be a beast sometimes, and this use case is no different. When it comes to slicing and dicing your data using dates and other time intelligence measures, there are many approaches, but this method for slicing data with time intelligence in Power BI excels at saving page space while producing dynamic results across multiple metrics with a click of a button.
Imagine you have a large sales dataset that a client would like to be able to slice according to industry-standard time periods, such as month-to-date, last year, etc. They’d like to quickly see the filtered data in their report without having to manually select or input date ranges for every single time period. Creating individual measures could attain this, but at the expense of a lot of slicers on the pages. Therefore, an aggregated Power BI date slicer needs to be created to consolidate all the different filters.
Building a Dynamic Time Slicer in Power BI
In our pursuit of building a better user experience (UX) and smoother navigation, we built an ad hoc slicer in Power BI to filter metrics by selective time periods (e.g., WTD, MTD, QTD, YTD, etc.). The purpose of this dynamic Power BI time slicer is to provide visually appealing navigation of the data and give the user a clear picture of their sales, leads, and collections on one page.
We’ve seen similar results in our client projects, such as in our Power BI Dashboard Case Study for Marucci Sports, where smart Power BI time intelligence functions and overall design simplified reporting for their leadership team.
When presented with the challenge, we researched standard solutions, but also rarer options since the requirements didn’t lend themselves to using the standard slicers in Power BI.
After some digging, we discovered a clever Power BI slicer time range solution often used in financial reporting—so naturally, we “borrowed” it for sales data. It’s ingenious and straightforward in theory, but in practice, the setup is rather time-consuming. Once completed, the resulting slicer makes date and time intelligence slicing a breeze.
Without further ado, let’s explore how to slice data with Power BI’s Time Intelligence function options. The main components of the slicer are the following: dynamic time intelligence measures, a DAX (Data Analysis Expressions) switch statement, and a table with a selector and reference column. The idea is to create a table with two columns (selector and reference) and DAX time intelligence functions that measure which filter applies to the data. Let me explain:
To illustrate this, we used the “Sales and Marketing Sample” dataset (aka World Wide Importers) from Microsoft’s Dashboard In A Day exercise. We loaded and edited the data just as outlined in the DIAD, but changed a few things:
For our purposes, we needed a full date table, which we created in M. The M code is below.
letSource =
Csv.Document(File.Contents(“C:UsersCDeNysschenDesktopDIADsales.csv”),[Delimiter=”,”, Columns=5, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#”Promoted Headers” = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#”Changed Type” = Table.TransformColumnTypes(#”Promoted Headers”,{{“ProductID”, Int64.Type}, {“Date”, type date}, {“Zip”, type text}, {“Units”, Int64.Type}, {“Revenue”, type number}}),
#”Appended Query” = Table.Combine({#”Changed Type”, #”International Sales”}),
#”Added Conditional Column” = Table.AddColumn(#”Appended Query”, “CountryName”, each if [Country] = null then “USA” else [Country]),
#”Removed Columns” = Table.RemoveColumns(#”Added Conditional Column”,{“Country”}),
#”Renamed Columns” = Table.RenameColumns(#”Removed Columns”,{{“CountryName”, “Country”}}),
#”Changed Type1″ = Table.TransformColumnTypes(#”Renamed Columns”,{{“Country”, type text}, {“Revenue”, Currency.Type}}),
#”Filtered Rows” = Table.SelectRows(#”Changed Type1″, each true)
in
#”Filtered Rows”
Then, since the slicer uses TODAY’s date as a reference and the dataset data only goes until December 31, 2018, we used the EDATE DAX function in a calculated column in the Sales table to shift the data up by 12 months. This step is not necessary for live data, but is for the purpose of this Power BI time intelligence demo.
UpdatedSalesDate = EDATE(Sales[Date],12)
Once this was done, we created a new relationship between ‘Date’[Date] and ‘Sales’ [UpdatedSalesDate] to feed the correct dates into our slicer logic.
These changes create the backbone of the data model, and the date table is required for the slicer to work. However, the disconnected table and associated measures are the sweet sauce for creating our dynamic Power BI time slicer.
The dataset can be accessed here: Microsoft Power BI Sample: Sales and Marketing.
Power BI Time Intelligence FAQs
Understanding time intelligence in Power BI takes practice, especially when building dynamic date models and slicers. Here are a few frequently asked questions that expand on key concepts and help you avoid common pitfalls.
How exactly do DAX time intelligence functions work in Power BI?
DAX time intelligence functions, such as TOTALYTD, SAMEPERIODLASTYEAR, and DATESINPERIOD, automatically calculate measures over specific intervals. These functions rely on a properly formatted date table to identify relationships between data points across days, months, and years. Power BI functions help users analyze growth trends, compare historical performance, and make more data-driven business decisions.
What happens if I don’t create a date table in Power BI?
Without a proper date table, Power BI time intelligence functions (like YTD, QTD, or MTD) can’t calculate correctly. The date table provides the structured calendar framework that DAX functions use to evaluate time-based data consistently. IronEdge’s solutions for creating effective Power BI dashboards take the hassle out of the process to ensure accurate reporting and predictable analytics.
Can I use time intelligence for fiscal years or non-standard calendars?
Yes, DAX time intelligence functions can handle fiscal calendars with your custom start and end dates. Start by defining your fiscal year logic in the date table, which enables report alignment with accounting or operational cycles instead of default calendar years.
What is the most critical requirement for time intelligence functions?
A continuous date column, which features a complete, unbroken range of dates, is required for success in Power BI. This ensures DAX time intelligence can correctly calculate cumulative metrics like growth over time or moving averages. Missing or duplicate dates can cause inconsistent results, and that’s why IronEdge uses expertly constructed Power BI date slicers and standardized data models for every client implementation.
Strengthen Your Power BI Strategy with Business Intelligence Support
Now that you’ve learned how to slice data with time intelligence in Power BI, you’re on your way to company-wide efficiencies. Want to improve your company’s strategies even further? Explore IronEdge Group’s full range of business intelligence and IT solutions to see how we can optimize your organization’s data strategy, cybersecurity, cloud-based upgrades, and more, all within our comprehensive managed IT services.
Eager to learn IronEdge’s advanced approach to data slicing for improved functionality across report pages? Check out our Power BI time slicer webinar and advanced training courses! Rather than hand off the complexities of Power BI to the pros? Reach out to our team. We’re here to help.
