DAX Date Calculations - Previous Period Combined Measure (Time Intelligence)

Intro

Weather there is a requirement for a combined time measure, for disconnected time dimension or similar date related calculation we can use time intelligence DAX functions and build our calculations in a relatively easy way.

Samples and Dev Environment

Environment:

• SQL Server and SQL Analysis Service running under the same account
• Visual Studio 2017
• SQL Server 2016

Samples: - Microsoft sample databases available at github:

• AdventureWorksDW2014
• Tabular model

Final solution for this demo:

• DAX time intelligence - Previous period Calculation

DAX Calculations

Calculation alternatives

To begin with DAX time measures, there are many time intelligence functions available in DAX and creating calculations based on those functions can be accomplished in different ways, which means usage of different functions can produce different results. 

In this case we will be focused only on previous period calculations and to demonstrate DAX diversity here is an example how to get the same results using different functions and calculation techniques in DAX:

Internet Total Sales PY:=

CALCULATE (

    'Internet Sales'[Internet Total Sales],

    DATEADD ( 'Date'[Date], -1, YEAR )

)

Internet Total Sales PY2:=

CALCULATE (

    'Internet Sales'[Internet Total Sales],

    PARALLELPERIOD ( 'Date'[Date], -1, YEAR )

)

Internet Total Sales PY3:=

CALCULATE (

    'Internet Sales'[Internet Total Sales],

    PREVIOUSYEAR ( 'Date'[Date] )

)

In a given example DATEADD , PARALLELPERIOD and PREVIOUSYEAR will return the same result and they can be use interchangeably. Also, in certain cases they might be different based on dataset or on intermediate measures used, so the recommendation is to check the best option between these and test for desired results.

Requirements

Business requirement: Create one calculation and no matter which period of date hierarchy is selected return previous period. For example, if date is selected then return previous day, if month is selected then return month, etc. 

Problem: How to get individual time calculation for previous period from Date table and combine them together into one calculation due to fact that there is no unique calculation available in DAX for this task.

Date hierarchy: Year-Semester-Quarter-Month-Date

Solution:
As mentioned previously, there are several ways how to get individual period calculations working. In this case we will use DATEADD for function that will work for the most of our calculations.

Quick Solution

Previous day:

Internet Total Sales PD:=
CALCULATE (
    'Internet Sales'[Internet Total Sales],
    DATEADD ( 'Date'[Date], -1, DAY )
)

Previous month:
Internet Total Sales PM:=
CALCULATE (
    'Internet Sales'[Internet Total Sales],
    DATEADD ( 'Date'[Date], -1, MONTH )
)

Previous quarter:
Internet Total Sales PQ :=
CALCULATE (
    'Internet Sales'[Internet Total Sales],
    DATEADD ( 'Date'[Date], -1, QUARTER )
)

Calendar semester ID - calculated measure in Date table
Calendar Semester ID = ('Date'[Calendar Year] *100) + 'Date'[Calendar Semester]

Previous semester ID - Helper measure in Date table to accommodate MAX function which normally doesn't work with dates and strings:

Previous Semester ID:=

CALCULATE (

    MAX ( 'Date'[Calendar Semester ID] ),

    DATEADD ( 'Date'[Date], -6, MONTH )

)

Internet Total Sales Semester:

Internet Total Sales Semester:=
CALCULATE (
    'Internet Sales'[Internet Total Sales],
    FILTER (
        ALL ( 'Date' ),
        'Date'[Calendar Semester ID] = MAX('Date'[Calendar Semester ID])
 )
)


Internet Total Sales Previous Semester:
Internet Total Sales PS:=
CALCULATE (
    'Internet Sales'[Internet Total Sales Semester],
    PARALLELPERIOD ( 'Date'[Date], -6, MONTH )
)

Internet Total Sales Previous Year:
Internet Total Sales PY:=
CALCULATE (
    'Internet Sales'[Internet Total Sales],
    DATEADD ( 'Date'[Date], -1, YEAR )
)

Calendar Level Selected - Created test measure in Date dimension to check which level of hierarchy is selected:

Calendar Level Selected:=

SWITCH (

    TRUE (),

    HASONEVALUE ( 'Date'[Date] ), "Date selected",

    HASONEVALUE ( 'Date'[Month Calendar] ), "Month selected",

    HASONEVALUE ( 'Date'[Calendar Quarter] ), "Quarter selected",

    HASONEVALUE ( 'Date'[Calendar Semester] ), "Semester selected",

    HASONEVALUE ( 'Date'[Calendar Year] ), "Year selected",

    BLANK ()

)

Internet Total Sales Previous Period:
Internet Total Sales Previous Period:=
SWITCH (
    TRUE (),
    HASONEVALUE ( 'Date'[Date] ), [Internet Total Sales PD],
    HASONEVALUE ( 'Date'[Month Calendar] ), [Internet Total Sales PM],
    HASONEVALUE ( 'Date'[Calendar Quarter] ), [Internet Total Sales PQ],
    HASONEVALUE ( 'Date'[Calendar Semester] ), [Internet Total Sales PS],
    HASONEVALUE ( 'Date'[Calendar Year] ), [Internet Total Sales PY],
    BLANK ()
)

Previous Period Calculation - Explained

Previous month

Previous month calculation is relatively simple to get and will follow the logic within the calculation from below: use Calculate function and do an offset for month before.

Internet Total Sales PM:=
CALCULATE (
    'Internet Sales'[Internet Total Sales],
    DATEADD ( 'Date'[Date], -1, MONTH )
)

All other levels of hierarchy except previous semester can be calculated in the same way by changing time frame parameter in DATEADD function. 

Previous Semester

There is more of work around semester because there is no built in function available for semester calculation. In this case, we need more calculation and here is one of the ways to get it in DAX:

1. Calendar Semester ID - calculated column in Date dimension to get numeric ID which will be used later in MAX function, which will not accept any of date or string inputs, but number parameters.
2. Previous Semester ID (Date table) - test measure to get previous semester by doing 6 months offset in the past and taking Semester ID for given output of previous filter.
3. Internet Total Semester - get previous period ID FILTER function: ALL ( 'Date' ) to take off the filter selection on currently selected semester, go over all date and find previous semester. 'Date'[Calendar Semester ID] = MAX('Date'[Calendar Semester ID]) - if Max is not specified, the comparison will go over all repeated rows in the table which is not allowed by DAX and it will report error because of scalar use, and that's why MAX will work bringing only distinctive values for comparison and in this context I like to think of MAX as current semester. 
4. Internet Total Sales - go back in time 6 months and for that current semester using [Internet Total Semester] measure. In this case, PARALLELPERIOD  has been used here instead of FILTER that we used before.

Calendar Semester ID = ('Date'[Calendar Year] *100) + 'Date'[Calendar Semester]

Previous Semester ID:=

CALCULATE (

    MAX ( 'Date'[Calendar Semester ID] ),

    DATEADD ( 'Date'[Date], -6, MONTH )

)

Internet Total Sales Semester:=
CALCULATE (
    'Internet Sales'[Internet Total Sales],
    FILTER (
        ALL ( 'Date' ),
        'Date'[Calendar Semester ID] = MAX('Date'[Calendar Semester ID])
 )
)

Internet Total Sales PS:=
CALCULATE (
    'Internet Sales'[Internet Total Sales Semester],
    PARALLELPERIOD ( 'Date'[Date], -6, MONTH )
)

Combined Previous Period Calculation

After all of the hard work that we've done on individual calculation comes the challenge how to put them all together into one calculation.

Test Date Hierarchy Level Selected

The following example demonstrate how to recognize date hierarchy level based on filter selection. SWITCH which has been used here can be treated as multiple IF functions and HASONEVALUE will detect if certain period is selected either from filter selection or it's present in row context.

Calendar Level Selected:=

SWITCH (

    TRUE (),

    HASONEVALUE ( 'Date'[Date] ), "Date selected",

    HASONEVALUE ( 'Date'[Month Calendar] ), "Month selected",

    HASONEVALUE ( 'Date'[Calendar Quarter] ), "Quarter selected",

    HASONEVALUE ( 'Date'[Calendar Semester] ), "Semester selected",

    HASONEVALUE ( 'Date'[Calendar Year] ), "Year selected",

    BLANK ()

)

Previous Period Measure

Finally, based on all of the individual measures and switch function to detect date hierarchy levels that we've created earlier, we can easily get our combined previous period calculation.

Internet Total Sales Previous Period:=
SWITCH (
    TRUE (),
    HASONEVALUE ( 'Date'[Date] ), [Internet Total Sales PD],
    HASONEVALUE ( 'Date'[Month Calendar] ), [Internet Total Sales PM],
    HASONEVALUE ( 'Date'[Calendar Quarter] ), [Internet Total Sales PQ],
    HASONEVALUE ( 'Date'[Calendar Semester] ), [Internet Total Sales PS],
    HASONEVALUE ( 'Date'[Calendar Year] ), [Internet Total Sales PY],
    BLANK ()
)