Date filter dax for N amount of years

[Anonymous]

I have column line chart which shows sales and profits. I have a slicer but I'm only using the end date and not using the start date because I just want to select one date. So, my chart show all the values from 2014 to 2019, but I only want the values from 2019 and 2018. Problem is this has to be dynamic, so when I pick an end date from the slicer I should only see the recent 2 years. For ex: Selected date - 05/10/2017, so time interval should be 01/01/2016 to 05/10/2017.

[Anonymous]

Well... First of all, it's a question of writing the right measure that will respect the slicer's selection and go back as far as you want. For all the other visible values present on the x-axis, it should return BLANK. However, it's a partial solution.

You can create something like "a dynamic range" of values in a dimension but that requires some specific techniques that you'll find in different blogs on DAX around the Net. There's no easy solution to this. A bit of creativity is needed.

However, it would be much easier to have a slicer with both ends (this is standard for a slicer that's connected to your Date table), where you can adjust the time boundaries independently in a continuous manner.

Best

Darek

[Anonymous]

Thanks Darlove. Currently, I'm lacking at the creativity part :). That's why I wanted to ask this to the community. I couldn't come up with a dynamic year range filter.

[Anonymous]

OK, mate. Here's the stuff.

First, the measure:

2Y Restricted Sales = 
var __allDates = ALLSELECTED( Dates[Date] )
var __maxDate = CALCULATE( LASTDATE( Dates[Date] ), __allDates )
var __firstDate =
    CALCULATE(
        STARTOFYEAR( SAMEPERIODLASTYEAR( LASTDATE( Dates[Date] ) ) ),
        __allDates
    )
var __effectiveDates = DATESBETWEEN( Dates[Date], __firstDate, __maxDate )
var __sales =
    CALCULATE(
        [Total Sales],
        KEEPFILTERS( __effectiveDates )
    )
return
    __sales

The measure [Total Sales] is just SUM( Sales[Amount] ). There are two tables Sales and Dates. Dates is a proper Date table that is joined to Sales via the Date field (present in both). Dates -> 1:* -> Sales. No cross-filtering.

When you drop the measure onto the column chart and create a slicer like this:The "Before" Slicer

you'll get what you asked for.

You'll notice that the slicer is the BeforeSlicer variety and the measure is constructed in such a way that it only returns non-blanks for units of time that are within [A, B], where B is the last day visible in the slicer and A is removed back to the beginning of the year that precedes the year of B.

Best

Darek

[Anonymous · Accepted solution]

Thank you for the reply Darek! 

This was just ridiculously easy. I was just thinking the very hard way of doing it :D. It feels embarassing too haha. Just changed the fiscal year visual filter to top N and added date column to the field then looked for top 2. Now, it works just like I wanted.

This is what happens when you spend too much time with complex measures and you forget about the easy ways of doing things in power bi 😄