DAX - Time Intelligence Functions

In my last post on DAX Function, I explained the commonly used DAX functions in Power BI and where they can be used, If you haven't checked it out, please check out the following link - https://www.crs4.online/post/dax-functions.

In this post, I am going to explain some of the Time Intelligence functions, why Time Intelligence? Time Intelligence Function help in manipulating the data for the time period, including days, months, years, and quarters that cater to comparing data between two time periods.

Let's see some of the Time Intelligence Functions and their uses.

• TOTALYTD / TOTALQTD / TOTALMTD

All the 3 functions perform similar functionality to the expression and filters applied to the dataset, but with a slight difference based on the use.

1. TOTALYTD - Performs year_to_date value based on the expression and filter applied to the given dataset. The result will be based on the expression (it may be an aggregate function) and the end_date (By default 31st December).

2. TOTALQTD - Performs Quarter_to_date value based on the expression and filter applied to the dataset.

3. TOTALMTD - Performs Month_to_date values based on the expression and filter applied to the dataset.

Syntax:

= TOTALYTD (<expression>,<dates> [,<filter>] [, <year_end_date>]) 

= TOTALQTD (<expression>, <dates> [,<filter>])
	
= TOTALMTD (<expression>, <dates> [,<filter>])

• ​expression - The Formula that returns a scalar value.

• ​dates - Field that contains the dates.

• ​filter - (Optional) A filter to apply for the active dataset.

• ​year_end_date - (Optional) By default end _date is December 31, else need to specify the month/day in quotes.

Note: The above function doesn't support direct query when used in Calculated columns or Row-Level Security.

I have used the Financial data file to provide some examples, we will see the result of the output in the below examples.

You can find the data in the file: Power Pivot --> Manage

Financial Data.xlsx

Download XLSX • 304KB

=TOTALYTD (
    SUM (financials [Sales]),
    financials [Date],
    financials [Country] = "Germany",
    "03/31"
 )

The above formula will result in the latest (2014) year sale value i.e. 14575824.78

Similarly, the TOTALQTD & TOTALMTD function works, you can find the example in the worksheet attached.

Alternative ways of using the TOTALYTD function through CALCULATE function and the calculate function are often used as alternative functions for most of the DAX functions.

The above example output can be achieved by using the formulation.

=CALCULATE (
    SUM (Sales [Sales_Amount]),
    DATESYTD (Date [Order Date], "03/31")
 )

In the next post, I will explain DATESYTD and its similar functions, stay tuned.