topic Re: Date Range based on Slicer selection in DAX Commands and Tips

Date Range based on Slicer selection

ragnezza — Tue, 05 Sep 2023 20:39:36 GMT

Hello - I'm looking to return a list of the previous "X" days based on a selected day from a slicer.

For example, once a day is selected in the 'Date' slicer, I want to return the date range in the visualization for the previous 180 days up to the selected day.

In simple words, the logic would be something like the below, but I cannot make it work:
IF ( Date >= SELECTEDVALUE ( Date )-180 || Date <= SELECTEDVALUE ( Date ) , Date , blank() )

And then use 'Date' in the visualization and exclude blanks.

Any recommendation is more than welcome, thanks!!

Re: Date Range based on Slicer selection

DataNinja777 — Tue, 05 Sep 2023 22:23:29 GMT

Hi @ragnezza,

What about using DATESINPERIOD function?

DATESINPERIOD function (DAX) - DAX | Microsoft Learn

Something like below is how it can be used:

Re: Date Range based on Slicer selection

ragnezza — Wed, 06 Sep 2023 14:29:49 GMT

Thanks DataNinja777 , this can help as it outputs the [Daily trends] for the previous 180 days, but it doesn't have any relation with the SELECTEDVALUE.
Below are two examples of how I would like this visualization to show based on the selected dates:

thanks!

Re: Date Range based on Slicer selection

DataNinja777 — Wed, 06 Sep 2023 20:22:31 GMT

Hi ragnezza ,

What about like below?

Re: Date Range based on Slicer selection

ragnezza — Wed, 06 Sep 2023 21:36:26 GMT

Thanks DataNinja777 . Your solutions provided a cumulative count for the last "X" days, while I was looking to include/exclude days based on the selection. I made it work with the following Measure and by adding a separate Table with just the 'Date' field, which is used as filter.

Measure_last_180_days =

IF (SELECTEDVALUE('Date'[Date])-180 <= MAX( 'Main Table'[Date]) &&

SELECTEDVALUE('Date'[Date]) >= MAX('Main Table'[Date]),"yes","no" )

Re: Date Range based on Slicer selection

ragnezza — Mon, 11 Sep 2023 13:28:29 GMT

Thanks DataNinja777 . This can work if I want to have the cumulative "Daily Trends" up to the selected day.
For this purpose I find this other solution:

Measure =
if( MAX( 'Main_Table'[Date]) >= SELECTEDVALUE('Date'[Date],min('Date'[Date]) )-180 &&
MAX( 'Main_Table'[Date]) <= SELECTEDVALUE('Date'[Date],max('Date'[Date]) ),
"Yes", "No" )

And then use this 'Measure' as a filter in the visualization.

Re: Date Range based on Slicer selection

Anonymous — Tue, 03 Oct 2023 09:02:49 GMT

Hi ragnezza ,

It seems that you have gotten a solution. Could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem as yours. Thank you.

Best Regards