Understanding DAX Time Intelligence with Additional Column Context

[SachinNandanwar]

The aritcle was originally published on : www.azureguru.net

Link to the original article :  link

In this short write up we will analyze how to evaluate additional columns along with DAX Time Intelligence functions if used together.

Lets take the following DAX query for example

DEFINE MEASURE Sales[PY_SalesAmount] = CALCULATE ( [Sales Amount], PARALLELPERIOD ( ( 'Date'[Date] ), -1, YEAR ) ) EVALUATE ADDCOLUMNS ( VALUES ( 'Date'[Calendar Year] ), "PY Sales Amount", [PY_SalesAmount], "Sales Amount", [Sales Amount] )

 In the query above, there is a defined a measure [PY_SalesAmount] that uses Time intelligence function PARALLELPERIOD to return Sales Amount of the previous year. In other words it shifts the current context of dates to one year back.

Given the interval type and number of intervals, we expect the above query to return the previous year sale amount along with the current year sales amount.

💡Note : Every Time Intelligence functions invokes context transition and executes in a filter context . You should also ensure that your Date table is ALWAYS marked as Date.

 and why should we mark date table as “date” ? and what would be the repercussions if we don’t ?

That is a separate topic for my new detailed upcoming blog.

Lets get back to our DAX query. Execute the earlier query on DAX.do and we have the following output. 

 So far so good. The DAX query returns Sales Amount from the prior year, through PARALLELPERIOD.

Now in addition to the above output, we might also want a output where we would like to display Brand names with the Year values.  

To achieve that ,we can’t just add the Brand column from the Product table because our table expression for ADDCOULUMNS is on the Date table and not the Product table.

Its like adding a column from a Product table in the SQL query that queries a Date table. Something similar like this.

Select ProductName,DateYear from DateTable

Changing our DAX query by replacing our table expression from Date to Product will also not give us Brand wise yearly sales.

That is because, both of our measures [PY_SalesAmount] and [Sales Amount] execute under the filter context of the Date and not on Product.

How to fix this ?

We will have to introduce filter context for Date and Product values together and make them available as table expression for ADDCOLUMNS.

This can be achieved by using SUMMARIZECOLUMNS function on Brand and Year values as SUMMARIZECOLUMNS always executes under filter context.

DEFINE
    MEASURE Sales[PY_SalesAmount] =
        CALCULATE ( [Sales Amount], PARALLELPERIOD ( 'Date'[Date], -1, YEAR ) )

EVALUATE
ADDCOLUMNS (
    SUMMARIZECOLUMNS ('Product'[Brand], 'Date'[Calendar Year]),
    "PY Sales Amount", [PY_SalesAmount],
    "Sales Amount", [Sales Amount]
)
ORDER BY 'Product'[Brand], 'Date'[Calendar Year]
  

For brevity lets add FILTERS in our query to discard out the empty values.

DEFINE
    MEASURE Sales[PY_SalesAmount] =
        CALCULATE ( [Sales Amount], PARALLELPERIOD ( 'Date'[Date], -1, YEAR ) )

EVALUATE
FILTER (
    ADDCOLUMNS (
        SUMMARIZECOLUMNS ( 'Product'[Brand], 'Date'[Calendar Year] ),
        "PY Sales Amount", [PY_SalesAmount],
        "Sales Amount", [Sales Amount]
    ),
    NOT ISBLANK ( [Sales Amount] )
)
ORDER BY
    'Product'[Brand],
    'Date'[Calendar Year]
  

That’s it. We have the desired output.

Conclusion

Though Time Intelligence functions invoke context transition, using them with other columns might not yield the desired results. Understanding how DAX functions behave under different contexts is key to writing efficient and accurate queries.

Thanks for reading !!!

[Nabha-Ahmed]

Great explanation! Really helpful for understanding how context works with DAX time intelligence

[Magudeswaran_MR]

@SachinNandanwar 
Thanks for sharing the context transition in DAX Time Intelligence Functions.