How to Use Time Intelligence in Power BI Using DAX

by | Updated December 23, 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

april

31mar01aprTraining Class: Financial Reporting W/Account Schedules for Dynamics 365 Business Central

07apr2:00 pm2:30 pmEnsuring Quality for Batch Manufacturers

08apr11:00 am12:00 pmConfab with Stoneridge - Livestream - Jumping into the Deep End of the Data Lake

14apr10:00 am10:30 amBoost Seller Productivity and Close More Business with Dynamics 365 Sales

21apr10:00 am10:30 amSetting Your Sales Team Up for Success: A Modern Look at Revenue Operations

21apr12:00 pm1:00 pmGetting Started with Barcode Scanners and WMS Apps for Business Central

21apr2:00 pm3:00 pmMoving to the Cloud: Today's Solution for Leading Field Service Companies

22apr11:00 am12:00 pmConfab with Stoneridge - Livestream - Moving from Manual to Automated with Strategic Automation Strategies

28apr10:00 am10:30 amThe Modern Manufacturer: Best Practices for Inventory Turnover

28apr12:00 pm1:00 pmPower Up Microsoft Dynamics by Automating Budgeting, Dashboarding and Reporting

may

05may10:00 am10:30 amAchieve More with Zetadocs for Business Central

05may2:00 pm2:30 pmSimplifying Item Pricing, Availability, and Tracing for Batch Manufacturers

06may11:00 am12:00 pmD365 Field Service – New Features to Empower Your Remote Workforce

12may10:00 am10:30 amThe Modern Manufacturer: Manufacturing Software in the Real World

12may12:00 pm12:30 pmFinancial Implications of Moving from Dynamics GP to Dynamics 365: Licensing and Infrastructure

17may(may 17)8:00 am28(may 28)11:00 amDynamics 365 Finance & Supply Chain Development Training - Online Workshop

26may10:00 am10:30 amThe Modern Manufacturer - Enterprise Asset Management

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