Advanced DAX Functions- Advanced Time Intelligence Functions

Hello learners, its time to learn about some advanced DAX functions, so let's deep dive today into the functionality of Advanced Time Intelligence Functions.

 Advanced Time Intelligence Functions

These functions allow you to build custom calendars, define dynamic date ranges, and compare performance across specific periods.

Some of the time intelligence functions are:

• Performance-to-date: For Example- DATESYTD
• Previous Period:  For Example- DATEADD
• Running Total: For Example- DATESINPERIOD

1. DATESYDT Function

DATESYDT returns a set of dates in the year up to the last date visible in the filter context.

Syntax:

=CALCULATE (Measure, DATESYTD (Calendar[DATE]))

For example: DATESYDT_fn = CALCULATE([AVERAGEX_Unit Cost], DATESYTD(Table1[OrderDate].[Date]))

Note: You can use DATESQTD and DATESMTD to get the set of dates in the quarters and months respectively.

2. DATEADD Function

DATEADD moves the given set of dates by a specified interval.

Syntax:

=CALCULATE (Measure, DATEADD (Calendar[Date], -1, MONTH))

For Example:  DATEADD_total 17 days ago = CALCULATE(SUM(Table1[Total]) , DATEADD(Table1[OrderDate].[DATE], -17, DAY))

Note: You can also use DAY, QUARTER or YEAR in place of MONTH as per your requirement.

3.DATESINPERIOD Function

This function returns the dates from the given period.

Syntax:

=CALCULATE (Measure,

 DATESINPERIOD(Calendar[Date], MAX (Calendar[Date]), -10, DAY))

For Example: 3 months Cum Total = CALCULATE(SUM(Table1[Total]), DATESINPERIOD(Table1[OrderDate].[Date], LASTDATE(Table1[OrderDate].[Date]), -3,MONTH))

4. PARALLELPERIOD Function

Returns a column of dates from a parallel period, by shifting the dates specified forward or backward in time based on a given interval (month/quarter/year)

Syntax:

=PARALLELPERIOD (Date, NumberOfIntervals, Interval)

For Example: Parallelperiod_sales last Yr = CALCULATE(SUM('Pen Sales'[Total Cost]), PARALLELPERIOD('Pen Sales'[SalesDate].[Date], -1, YEAR))

The result would be something similar to the attached table.

5. PREVIOUSQUARTER Function

Returns a table containing a column of all dates from the previous quarter, based on the first date in the date range specified

Syntax:

=PREVIOUSQUARTER (DATES)

For Example: Previousquarter_fn = CALCULATE(SUM('Pen Sales'[Total Cost]), PREVIOUSQUARTER('Pen Sales'[SalesDate].[Date]))

The resultant values generated is shown with the help of a table.

6.SAMEPERIODLASTYEAR Function

Returns a set of dates in the current selection from the previous year

Syntax:

=SAMEPERIODLASTYEAR(DATES)

For example: sameperiodlastyear_fn = CALCULATE(SUM('Pen Sales'[Units packs]), SAMEPERIODLASTYEAR('Pen Sales'[SalesDate].[Date]))

In this clustered column chart, “unit packs” is the current data and it has shown the comparison with the unit packs sold in the same period but last year.