How to Use Time Intelligence in Power BI Using DAX

by | Updated October 15, 2020 | Power Platform

In my last blog, I introduced DAX and we created a column and measure. In this blog post, we’ll go over how to use time intelligence in Power BI using DAX. Time intelligence functions enable you to manipulate data using time periods such as years, quarters, months, and days and creating calculations over those time periods. The most common time periods that we encounter in business scenarios are usually Year-to-Date, Quarter-to-Date, Month-to-Date, Last Year Full Year, and Rolling 12 Months. There are many other time intelligence functions in DAX. You can find the entire list of time intelligence functions here.

To use time functions in Power BI, you need to have a table that contains at least one column that has a data type of Date. This column should have only one row per day for all the range of dates with no gaps. In our example, we have a “Calendar” dimension that contains a column called “Date” that is a type of Date column.

Now that we have our requirements for using Time Intelligence functions in Power BI let’s start creating some. We’ll create these calculations in our Sales table.

Year-to-Date Revenue

Year-to-date revenue in Power BI.

Enter this in the text bar: Sales Revenue YTD = TOTALYTD(SUM(‘Sales'[SalesAmount]),’Calendar'[Date])
The syntax is TOTALYTD(<expression>,<dates>[,<filter>][,<year_end_date>]).

The TOTALYTD function returns the year-to-date based on the latest date in our calendar dimension or a year_end_date that we specified. SUM function will add all the number sin the SalesAmount column.

Quarter-to-Date Revenue

Quarter-to-date revenue in Power BI.

Enter this in the text bar: Sales Revenue QTD = TOTALQTD(SUM(‘Sales'[SalesAmount]),’Calendar'[Date])
The syntax is TOTALQTD(<expression>,<dates>[,<filter>]).

The TOTALQTD function returns the quarter-to-date based by evaluating al dates in the current quarter to date. The filter is optional to apply to the current context.

Month-to-Date Revenue

Month-to-date revenue in Power BI.

Enter this in the text bar: Sales Revenue MTD = TOTALMTD(SUM(‘Sales'[SalesAmount]),’Calendar'[Date])

The syntax is and function is the same as TOTALQTD except this is for the month to date.

Last Year Revenue

Last year revenue in Power BI.

This calculation is a little different than the previous three. In this case, we have to use a function called SAMEPERIODLASTYEAR which shifts the date one year back in time from the date specified in our date column. We’re also using the CALCULATE function instead of SUM because CALCULATE allows us to change the filter context and use SAMEPERIODLASTYEAR as a filter.

LY YTD Sales = CALCULATE(‘Sales'[Sales Revenue],SAMEPERIODLASTYEAR(‘Calendar'[Date].[Date]))

Finally, we’ll calculate Rolling 12 Months revenue.

Rolling 12 months revenue in Power BI.

Rolling 12 Months is a lot different than the other time calculations we’ve done so far. There are other ways to achieve this calculation, however, we’ll use the one in this example. We’re also using the Sales Date from our Sales fact table rather than the Calendar Date.
Revenue TTM =
CALCULATE(
‘Sales'[Sales Revenue]
,DATESINPERIOD(
‘Sales'[SalesDate]
,MAX(‘Sales'[SalesDate])
,-12
,MONTH
)
)

Let’s read the expression from the inside out. We’re taking the maximum Sales Date from the Sales fact table to give us an endpoint in the data. After that, the DATESINPERIOD time intelligence function (DATESINPERIOD(<dates>,<start_date>,<number_of_intervals>,<interval>)) contains a column of dates that begin with the start_date, in our case the latest date we have a sales record in our Sales fact table, and continues for the specified number_of_intervals. Since we’re looking at a rolling 12-month period our number of intervals is 12. Also, notice in the DATESINPERIOD function we specified the interval by which to shift the dates as month. We could use year, quarter, or day as well.

Time intelligence functions in Power BI enable us to create calculations that span multiple time periods and relative time periods. We saw how to create to-date calculations for year, month, and quarter. Furthermore, we expanded our calculations to last year and rolling 12 months. These functions provide us with a lot of flexibility in reporting on time. These time intelligence functions can be utilized at any Power BI license level, but a Power BI Pro license is required to share data and reports with other Pro license holders. Click here to read more about Power BI Free vs Pro. Additionally, you can increase your reporting specificity and improve your data visualization by setting up a Power BI Hierarchy.

Related Posts

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

Upcoming Events

october

07oct12:00 pm1:00 pmThe Three Paths to Business Central from Dynamics GP

08oct11:00 am12:00 pmConfab with Stoneridge - Livestream - The Vision and Strategy of Microsoft Business Systems

14oct10:00 am10:30 amThe Modern Manufacturer - Managing Complex Cost Modeling

14oct12:00 pm12:30 pmGenerating Custom Inspection or Process Forms

19octAll Day22Stoneridge Connect Fall 2020

22oct11:00 am12:00 pmConfab with Stoneridge - Livestream - Stoneridge Connect Recap

28oct10:00 am10:30 amThe Modern Manufacturer - Engineering Change Management: Introduction of NEW Functionality for Manufacturers Using Dynamics 365

november

11nov10:00 am10:30 amThe Modern Manufacturer - Tears and Trauma of MRP

About Stoneridge
Stoneridge Software is a unique Microsoft Gold Partner, with emphasis on partner. With specialties in Microsoft Dynamics 365, Microsoft Dynamics AX, Microsoft Dynamics NAV, Microsoft Dynamics GP and Microsoft Dynamics CRM, we focus on attracting the most knowledgeable experts in the field to our team, and prioritize delivering stellar solutions with maximum impact for your business. At Stoneridge, we are deeply committed to your results. Each engagement is met with a dedicated team, ready to provide thorough, tailored, and expert service. Based in Minnesota, we intentionally “step into your shoes,” wherever you are. We focus on what you care about, and develop trusting, long-term relationships with our clients.

Subscribe To Our Blog

Sign up to get periodic updates on the latest posts.

Thank you for subscribing!

X