How to show more dates than selected in DAX | by Salvatore Cagliari | Jun, 2022

By Jessie Hobb On Jun 17, 2022

What if your users want to select one year but see four years of data based on the selected year? Let’s see how to build a solution to tackle this requirement.

Photo by Tingey Injury Law Firm on Unsplash

One of my clients asked me to build a report in Power BI with a Slicer for the reporting period and a visual, which shows four years of data based on the selection in the Slicer. Two years before the selected period, the selected year, and one year after the selected year.

When we look at a time series, the data could look like the table in the following picture, which contains random data as an example:

Figure 1 — Example data (Figure by Author)

As you can see, this dataset is based on months instead of years. But I use this table only to show you the requested output.

Based on this data, we would get this report when we select January of 2022, and we want to get six months:

Figure 2 — Example report (Figure by Author)

Now, I will go through different approaches to solve this request.

This requirement presents not only a DAX challenge but a data modelling challenge as well. Our current data model with one central Date table will not support such a report.

One way to solve the request is to create a new calculated column in the date table:

“Actual6years”, IF ( YEAR ( [Date] ) >= YEAR(Now()) — 3
&& YEAR ( [Date] ) <= YEAR(Now()) + 2
,1
,0 ),

Now, you can use this column to filter your visuals and get the data you need.

Unfortunately, this approach is not viable, as the calculated column is calculated only once, at Refresh time.
This column is based on the last Refresh date. Therefore, a Slicer on the date table will not change anything.

Before we continue, we need to take some time to think about our data model.

The tables involved in this request are the following two:

Figure 3 — Starting Data model (Figure by the Author)

When we think about how filtering works, we realise that this data model will not allow us to build a report as needed.

The problem is that as soon as we filter one year from this Date table, we will no longer see any other year in the Filter context.

And, when we remove the filter context, we will not get the desired result either.

For example, look at the following Measure:

Sum Sales Amount 6 years =  VAR SelectedYear = SELECTEDVALUE(‘Date’[Year])  VAR FirstYear = SelectedYear — 3  VAR LastYear = SelectedYear + 2  VAR SelectedDateList = DATESBETWEEN(‘Date’[Date]
,DATE(FirstYear, 1, 1)
,DATE(LastYear, 12, 31)
)  VAR Result = CALCULATE([Sum Online Sales]
,REMOVEFILTERS(‘Date’)
,SelectedDateList
)RETURN
Result

Here is the result:

Figure 4 — Result of simple Measure with one Date table (Figure by the Author)

This result is that the Filter set with the SelectedDateList Variable replaces any existing filter on the Date table. Now CALCULATE() resets the Filter Context to include all Years between the FirstYear and the LastYear Variables.

The following approach was inspired by a solution described by Alberto Ferrari from SQLBI a few months ago.
You can find the original YouTube videos and the corresponding article in the References section at the end.

The trick is to add a copy of the Date table:

Figure 5 — Data model with the Selection Date table (Figure by the Author)

Now, I can use the Selection Date table in all Slicers and the Date table in my Visuals. With the CROSSFILTER() function, I can choose to Disable the Relation between the selection Date and the Date table to manipulate the Filter Context.

I can choose not to manipulate the Filter Context, and all Measures will continue working as before.

Now we will see how I can use this Data model to solve the request.

OK, let’s write our first Measure.

This is the Measure to calculate the first and the last year needed in the report. Then, we pass the dates between these two years to a CALCULATE() function:

Sum Sales Amount 6 years =  VAR SelectedYear = SELECTEDVALUE(‘Selection Date’[Year])  VAR FirstYear = SelectedYear — 3  VAR LastYear = SelectedYear + 2  VAR SelectedDateList = DATESBETWEEN(‘Date’[Date]
,DATE(FirstYear, 1, 1)
,DATE(LastYear, 12, 31)
)VAR Result = CALCULATE([Sum Online Sales]
,SelectedDateList
)RETURN
Result

As you can see, I use the SELECTEDVALUE() function to get the selected Year from the Selection Date table.

Then, I get the needed Years from the Date table.

The result is the following:

Figure 6 — Result of DAX Measure with Selection Date table (Figure by the Author)

Now we see the same (wrong) result for each year.

The reason is the same as before. Because we replace the Filter Context on the Date table with the content of the SelectedDateList Variable, we see the same result for each Year in the Table.

So, we have to follow a slightly different route.

To build the working solution, I must change my approach:

I must not replace the current Filter Context on the Date table
I must remove the Filter Context from the Selection Date table
I must calculate the result for each year from the Date table

As mentioned above, I can use the CROSSFILTER() function to Disable the Relationship between the Selection Date and the Date table. This removes the Filter on the Date table without removing the Filter Context on the Date table.

To calculate the result for each year, I can use the IF() function.

This is the Measure with the correct solution:

Sum Sales Amount 6 months =  VAR SelectedYear = SELECTEDVALUE(‘Selection Date’[Year])  VAR FirstYear = SelectedYear — 3  VAR LastYear = SelectedYear + 2  VAR Result = CALCULATE([Sum Online Sales]
,CROSSFILTER(‘Selection Date’[DateKey]
,‘Date’[DateKey]
,None)
)  VAR ActualDateYear = CALCULATE(SELECTEDVALUE(‘Date’[Year])
,CROSSFILTER(‘Selection Date’[DateKey]
,‘Date’[DateKey]
,None)
)RETURN
IF(ActualDateYear >= FirstYear &&
ActualDateYear <= LastYear
,Result
)

In this solution, I don’t use the DATESBETWEEN() function to build a list of Dates between the first and the last year. But I use the Variables in the IF() function to make sure to calculate the result only for the needed years.

You can see the result in the following picture:

Figure 7 — Result of the solution (Figure by the Author)

To understand this Measure correctly, you need to look at the result on a row-by-row basis.
For example, look at the row for 2017.

The Measure gets the selected year from the Selection Date Slicer (2016). It calculates the first and the last year, for which the result needs to be calculated (The Variables SelectedYear, FirstYear & LastYear).

The Variable Result contains the Result of the Base Measure [Sum Online Sales] but disables the Relationship between the Selection Date and the Date Tables.

Now, I can get the result for all years without further restrictions.

For this reason, I use SELECTEDVALUE to assign the actual year from the Date table, used in the Visual, to the Variable ActualDateYear.
Remember that the Filter Context on the Date table remains unchanged.

Now, I can use a simple IF() to define for which years the result needs to be displayed.

As you can see, the PY Measure still works without any further modification, as the filter on the Selection Date Table filters the data in the Date Table. This filter is then used in the PY Measure.

The Total row is empty, as there is no Filter Context from the Selection Date and the Date table. Therefore, the Measure cannot return any value.

If you want to show a result in the Total row, you can add another IF() to the Measure, enclosing the existing IF(), to define the needed result for the Total using the ISFILTERED() function.

Anyway, there is still room for improvement.

For example, what happens when a User selects more than one Year in the Selection Date table?

We have to define which year we have to use as the reference year in this case.

We can use the following Measure when we want to use the last selected year:

Sum Sales Amount 6 Years MultiYear =  VAR SelectedYear = MAX(‘Selection Date’[Year])  VAR FirstYear = SelectedYear — 3  VAR LastYear = SelectedYear + 2  VAR Result = CALCULATE([Sum Online Sales]
,CROSSFILTER(‘Selection Date’[DateKey]
,‘Date’[DateKey]
,None)
)  VAR ActualDateYear = CALCULATE(SELECTEDVALUE(‘Date’[Year])
,CROSSFILTER(‘Selection Date’[DateKey]
,‘Date’[DateKey]
,None)
)RETURN
IF(ActualDateYear >= FirstYear &&
ActualDateYear <= LastYear
,Result
)

You can use the MIN() function for the first selected year as the reference year.

The result of this Measure looks like this:

Figure 8 — Result with a multi-Year selection (Figure by the Author)

Another interesting scenario is when the user wants to select a month instead of a year.

How can we solve this?

Again, we must define what the selection of a month means.

Does it mean the first or the last day of the month?

Depending on this decision, we combine the EOMONTH(), MAX() and the EDATE() functions to calculate the desired result.

If we want to use the last day of the selected month as the reference date, we can use the following Measure:

Sum Sales Amount 8 months =  VAR MaxSelectedMonth = EOMONTH(MAX(‘Selection Date’[Date]), 0)  VAR Prev5Months = EDATE(MaxSelectedMonth, -5)  VAR Next2Months = EDATE(MaxSelectedMonth, 2)  VAR Result = CALCULATE([Sum Online Sales]
,CROSSFILTER(‘Selection Date’[DateKey]
,‘Date’[DateKey]
,None)
)  VAR ActualDate = CALCULATE(
MAX(‘Date’[LastDayOfMonth])
,CROSSFILTER(‘Selection Date’[DateKey]
,‘Date’[DateKey]
,None)
)RETURN
IF(ActualDate >= Prev5Months &&
ActualDate <= Next2Months
,Result
)

When assigning a value to the ActualDate Variable, I use the LastDayOfMonth column from my Date table.
This column contains the Last Day of the Month for each row in the Date table. With this column, I don’t have to use the EOMONTH() function in the Measure to get the same result.

The result of the Measure looks like this:

Figure 9 — Result of the month selection (Figure by the Author)

The solutions shown here are good examples of how we can combine a good data model and our understanding of the Filter Context and how we can manipulate it, to solve different scenarios.

Although the solution provided by SQLBI would work as well, it has some drawbacks:

I must decide which table can be used for which Slicer on which page
Depending on the combination, I have to change the Measures to cope with the two Date tables

Anyway, the solution shown here can also be used to create a Calculation Item, as we work with a base Measure to get the Result.

Regardless of which solution you implement, you need to explain to the users of your data model how they must use the two Date Tables.

There is the potential to get the wrong result if the two Date tables are not used as intended.

Thank you for reading.

The solution explained here was inspired by these resources from SQLBI:

and the corresponding article from the SQLBI website:

Show previous 6 months of data from single slicer selection — SQLBI

Here is the follow-up YouTube video with some optimisations:

I use the Contoso sample dataset, like in my previous articles. You can download the ContosoRetailDW Dataset for free from Microsoft here.

The Contoso Data can be freely used under the MIT License, as described here.

I enlarged the dataset to make the DAX engine work harder.
The Online Sales table contains 71 million rows (instead of 12.6 million rows), and the Retail Sales table contains 15.5 million rows (instead of 3.4 million rows).