How to return one value underneath the max value of a date slicer?

[GM30]

Is there a way to create a filter, measure, or column that would reference the max date in a slicer, then return the date of the last day prior within the dataset? 

One difficulty to my problem is that my date values are only business days (monday through friday). So, if I were to set the max value of the slider to 2/5/24 (a monday) I would want the value for 2/2/24 (the previous friday).

Any help or suggestions would be greatly appreciated!

[Anonymous · Accepted solution]

Hi @GM30 ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

(2) We can create a slicer table.

SlicerTable = VALUES('Table'[Time Series])

(3) We can create a calculated column and a measure.

Index = RANKX('Table','Table'[Time Series],,ASC,Dense)

Time = 
var _date=CALCULATE(MAX('SlicerTable'[Time Series]),ALLSELECTED('SlicerTable'))
 var _a=CALCULATE(MAX('Table'[Index]),FILTER(ALLSELECTED('Table'),'Table'[Time Series]=_date))
 var _b=CALCULATE(MAX('Table'[Time Series]),FILTER(ALLSELECTED('Table'),'Table'[Index]=_a-1))
 RETURN _b

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[GM30]

Is there a way to keep the 1 beneath the max value while using a date slicer that is set to between? As I have other metrics on the page that are calculated over a period of time (such as a year)

[selimovd]

Hey @GM30 ,

how does your data table look like?

Best regards

Denis

[GM30]

The data looks like the following although the avg fields are calculating their respecitve averages:

My main goal is to retrieve the 1 date below the max date according to an adjusting slicer. Then, I want to use that value to filter cards that display the Q1_AVG and Q2_AVG values.

Time Series:	Month 1	Month2	Month3	Month4	Month5	Month6	Q1_AVG	Q2_AVG
2/6/24	1	2	3	4	5	6	AVG(month1,month2,month3)	Avg(month4,month5,month6)
2/5/24	1	2	3	4	5	6
2/2/24	1	2	3	4	5	6
2/1/24	1	2	3	4	5	6