Specific date filter

DAX DAX Calculations
1

Hello everyone
I’ve got a fact table with 4 years of data. That Sales table is connected with dates.
I would like to have on my report, the filter dates but, only with actual year, i mean, 01/01/2021 and 22/02/2021. I also want to be able to change the date filter if i want, like from 1/1/2020 till 31/12/2020.
One last thing. As you may already have guessed, this report hast visuals and data from all these years.

Let me know if you have any questions.
Thanks for your help
Pedro

2

Hello @pedroccamara,

Thank You for posting your query onto the Forum.

Well since you want to load the past data into the data model and than want to analyze the numbers periodically. So in this case, you can put the years into the FILTER PANE menu under the “Filters On This Page” or “Filters On All Pages” and than just select the 2021 if you want to analyze the numbers for 2021 to till date and if you want to analyze the numbers for previous year than de-select the option of 2021 from the FILTER PANE and just select 2020. Simple!!!

Hoping you find this useful. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

1 Like
3

@pedroccamara,

I use a construct like what you describe in a lot of my reports. Here’s what I do:

  1. Add the following calculated column to my Dates table:

    Active Dates =

     VAR MinDate = DATE( 2020, 1, 1 ) 
 VAR MaxDate = TODAY() 
 VAR Result = 
 IF(
     AND(
         Dates[Date] >= MinDate,
         Dates[Date] <= MaxDate
     ),
     1,
     0
 ) 

 RETURN
 Result

  2. Create a hierarchy slicer for Year, Month and Date

  3. Set a filter on that slicer for [Active Dates] = 1

image

Presto! Dynamic filtered heirarchy slicer:

image

I hope this is helpful. Full solution file posted below.

1 Like
4

A hybrid for methods above if you use the Extended Date table Just place a Filter On This Page or Filter On All Pages and set the YearOffset =0 That ensures the “current year” is selected at all times no need to ever update the filter again…

Should you want the previous year as well select the YearOffset =0 and =-1

1 Like
5

@Melissa,

Great point about using the Extended Date Table fields. However, I think to get the desired result here, you’d also have to add the following filter:

image

  • Brian
2 Likes
6

Yes!
You really can read my mind …

:innocent:

1 Like
7

which i’ve found in this Sam’s video

You guys are the best
Thank you so much!!!

8

Hi @pedroccamara I hope that you are having a great experience using the Support Forum so far. We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

1 Like