Skip main navigation

New offer! Get 30% off one whole year of Unlimited learning. Subscribe for just £249.99 £174.99. New subscribers only T&Cs apply

Find out more

Work With Time Intelligence


Work With Time Intelligence

All data analysts will have to deal with time. Dates are important, so we highly recommend that you create or import a dates table. This approach will help make date and time calculations much simpler in DAX.

While some time calculations are simple to do in DAX, others are more difficult. For instance, the following screenshot shows what happens if you want to display a running total.

Notice that the totals increment for each month but then reset when the year changes. In other programming languages, this result can be fairly complicated, often involving several variables and looping through code. DAX makes this process fairly simple, as shown in the following example:

YTD Total Sales = TOTALYTD ( SUM('Sales OrderDetails'[Total Price]) ,
Dates[Date] )

The YTD Total Sales measure uses a built-in DAX function called TOTALYTD. This function takes an argument for the type of calculation. You can use the SUM function to get the Total Price, as you’ve done throughout this module. The second argument that you want to operate over is the Dates field. You can use your Dates table and add this measure to your visual, and you’ll get the running total result that you’re looking for. You can use all functions with YTD, MTD, and QTD in a similar fashion.

This article is from the free online

Microsoft Power BI: Advanced Data Analysis and Visualisation

Created by
FutureLearn - Learning For Life

Reach your personal and professional goals

Unlock access to hundreds of expert online courses and degrees from top universities and educators to gain accredited qualifications and professional CV-building certificates.

Join over 18 million learners to launch, switch or build upon your career, all at your own pace, across a wide range of topic areas.

Start Learning now