topic Re: How to subtract 3 months from a Date, then find nearest possible date from a slicer. in Desktop

How to subtract 3 months from a Date, then find nearest possible date from a slicer.

Deermeat — Sat, 30 Apr 2022 00:12:35 GMT

Hello,

I currently have a list of dates in a slicer in single select:

Today's Date (Ex: 2 May 2022)
29 April 2022
31 Mar 2022
28 Feb 2022
31 Jan 2022
31 Dec 2021
30 Nov 2021
29 Oct 2021
30 Sep 2021
All the way to 2011.

The list consist of today's date and the date for end of each month. However, on months that ends on a weekend, it will have the date on Friday instead of the weekend. Example: April 2022, ends on 30 April 2022, but list has 29 April 2022.

What I'm trying to do is when a date is chosen in the slicer, I want to subtract 3 months from that date and find the nearest possible date (this is necessary due to some dates ending on Friday instead of weekend issue).

Anybody has any idea how I can do this?

Sample Dataset: https://drive.google.com/file/d/1sy5naT4OTcBUd9fcLJJG7KaFxXgSdPUB/view?usp=sharing

Thank you for your help!

Re: How to subtract 3 months from a Date, then find nearest possible date from a slicer.

hnguy71 — Sat, 30 Apr 2022 01:21:39 GMT

Hey Deermeat ,

I think the easiest quick win method is to add a rank to your slicer table:

DateRank = RANKX(FILTER(Slicer, [Date] < EARLIER([Date])), [Date],,ASC)

Then in a measure you'd just ask to return the rank and subtract by 3:

DateMinusThree = VAR _SelectedRank = SELECTEDVALUE(Slicer[DateRank], MAX(Slicer[DateRank])) VAR _EvalRank = _SelectedRank - 3 VAR _GetRank = IF(_EvalRank < 1, 1, _EvalRank) VAR _GetDate = CALCULATE(MAX(Slicer[Date]), Slicer[DateRank] = _GetRank, REMOVEFILTERS(Slicer)) RETURN _GetDate

Re: How to subtract 3 months from a Date, then find nearest possible date from a slicer.

Deermeat — Sat, 30 Apr 2022 01:31:38 GMT

hnguy71

Thanks for the reply. Could I know what exactly is a slicer table and how this can be created? My slicer is just by itself without a table? Its generated from a column in my original data table right?

Re: How to subtract 3 months from a Date, then find nearest possible date from a slicer.

hnguy71 — Sat, 30 Apr 2022 01:47:25 GMT

Hi Deermeat ,

That's somewhat correct. It's primarily a disconnected table possibly containing all your dates from one or many tables. These dates can be derived from multple sources and it's ultimately up to you on how you create your slicer table. Here's a sample:

SlicerTbl = VAR _Dates = DISTINCT(FactTable[Date]) // Substitute this for your dates from fact table. RETURN GENERATE( _Dates, ROW("RankDate", RANKX(FILTER(_Dates, [Date] < EARLIER([Date])), [Date],,ASC)) )

Re: How to subtract 3 months from a Date, then find nearest possible date from a slicer.

Deermeat — Sat, 30 Apr 2022 02:10:53 GMT

Hi hnguy71,

Thank you very much. I understand how to get the independent table to work, however when I used the measure formula after adapting it to my report. It keeps displaying the same date without change.

Do you know why this might be the case?

Re: How to subtract 3 months from a Date, then find nearest possible date from a slicer.

hnguy71 — Sat, 30 Apr 2022 02:14:05 GMT

Deermeat , you would have to supply a sample. I can't immediately tell what the issue may be.

Re: How to subtract 3 months from a Date, then find nearest possible date from a slicer.

Deermeat — Sat, 30 Apr 2022 02:17:45 GMT

hnguy71

here's a link to the file!

https://drive.google.com/file/d/1niMibueGHS-L6i0FeX8fyh6nZc0PZKHf/view?usp=sharing

Re: How to subtract 3 months from a Date, then find nearest possible date from a slicer.

hnguy71 — Sat, 30 Apr 2022 02:22:15 GMT

Deermeat ,

I see the issue. You're using the Date from your Sheet1 table. Instead, your date needs to be from the SlicerTbl.

Re: How to subtract 3 months from a Date, then find nearest possible date from a slicer.

Deermeat — Sat, 30 Apr 2022 02:27:43 GMT

hnguy71,

Sorry, which part did I use the Date from Sheet1? The Code that I used for DateMinusThree is all using SlicerTbl Date:

DateMinusThree = VAR _SelectedRank = SELECTEDVALUE(SlicerTbl[RankDate], MAX(SlicerTbl[RankDate])) VAR _EvalRank = _SelectedRank - 3 VAR _GetRank = IF(_EvalRank < 1, 1, _EvalRank) VAR _GetDate = CALCULATE(MAX(SlicerTbl[Date]), SlicerTbl[RankDate] = _GetRank, REMOVEFILTERS(SlicerTbl)) RETURN _GetDate

Re: How to subtract 3 months from a Date, then find nearest possible date from a slicer.

hnguy71 — Sat, 30 Apr 2022 02:34:58 GMT

Deermeat ,

In your slicer visual, you're using the date from Sheet1. You need to replace the field in your visual to your SlicerTbl[Date] field.

Re: How to subtract 3 months from a Date, then find nearest possible date from a slicer.

Deermeat — Sat, 30 Apr 2022 02:37:49 GMT

hnguy71,

Omg, it works now. Thank you so much for your help!

Re: How to subtract 3 months from a Date, then find nearest possible date from a slicer.

Deermeat — Sat, 30 Apr 2022 02:54:44 GMT

hnguy71,

Could I ask you one last question. When I set the slicer to use the SlicerTbl's Date, it broke the chart that is on the right. It no longer can be filtered by the slicer.

I tried connecting a relationship between Sheet1 Date and SlicerTbl Date, but this breaks my 3MthsAgo Average Score Measure:

3MthsAgo Average Score = VAR _SelectedRank = SELECTEDVALUE(SlicerTbl[RankDate], MAX(SlicerTbl[RankDate])) VAR _EvalRank = _SelectedRank - 3 VAR _GetRank = IF(_EvalRank < 1, 1, _EvalRank) VAR _GetDate = CALCULATE(MAX(SlicerTbl[Date]), SlicerTbl[RankDate] = _GetRank, REMOVEFILTERS(SlicerTbl)) VAR _XMonYear = YEAR(_GetDate) VAR _XMonMonth = MONTH(_GetDate) RETURN CALCULATE( AVERAGE( Sheet1[Score] ), ALL( Sheet1[Score] ), YEAR(Sheet1[Date]) = _XMonYear, MONTH(Sheet1[Date]) = _XMonMonth)

Do you know how this can be fixed?

New Data: https://drive.google.com/file/d/12k-dHYN0YcgeRs1DkpqbVDAGcHT-iZTJ/view?usp=sharing

Re: How to subtract 3 months from a Date, then find nearest possible date from a slicer.

hnguy71 — Sat, 30 Apr 2022 03:01:22 GMT

Deermeat , it seems to be working. Could you describe to me the issue?

Re: How to subtract 3 months from a Date, then find nearest possible date from a slicer.

Deermeat — Sat, 30 Apr 2022 03:04:15 GMT

hnguy71,

The graph on the left is showing too many points. It shows all possible points across the 1 year data, instead of according to the slicer. The slicer with Date from SlicerTbl allow the measure 3MthsAgo Average Score to work, but it does not filter the graph. The slicer with Date from Sheet1 allow the graph to be filtered, but does not work for the measure.

I have managed to 'fix' this by syncing the two slicer together and hiding one of them. A bit janky, but it works now.

Thank you for your help!