# Understanding DAX Time Intelligence with Additional Column Context

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

```css
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.

<div data-node-type="callout">
<div data-node-type="callout-emoji">💡</div>
<div data-node-type="callout-text"><strong>Note : </strong>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.</div>
</div>

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1757687718630/5c76f7d2-873d-4489-af6e-09449715f0f7.png align="center")

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](http://DAX.do) and we have the following output.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1757687888506/49be6df9-b413-48bd-bd54-2eaaba85ac55.png align="center")

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.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1757705424073/c75cc054-9d0e-4c0e-86ba-2eaadf04a46a.png align="center")

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

```sql
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.

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

EVALUATE
ADDCOLUMNS (
    VALUES ('Product'[Brand]),  
    "PY Sales Amount", [PY_SalesAmount],
    "Sales Amount", [Sales Amount]  
)
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1757689791714/88cd3fbb-bd91-4c00-aa2b-51be855bbabc.png align="center")

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.

```css
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]
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1757703416423/4fdccac1-8cba-4880-a231-c0b442dc54fa.png align="center")

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

```css
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]
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1757703743772/40be4cfa-4efd-41f4-9509-586452657af1.png align="center")

That’s it. We have the desired output.

### Conclusion

Though Time intelligence functions invokes context transition ,using them with other columns might not get the desired results that you would expect. A decent level of understanding on how DAX functions execute under different contexts is desirable.

Thanks for reading !!!