Trying to pull the day prior and day after values from a slicer. Is this possible?

[Anonymous]

I'm finished working on a currency exchange rate table based upon the day.

I have a date slicer (Currecny Exchange Rate_R [Date Slicer]) (I changed this to a text format so I could add the search option to the slicer) and it was requested that I add the day prior value and the day after vaule to the finished table.

The Values are (Currency Exchange Rate_R [AVG]) & (Currency Exchange Rate_R [SPOT])

I've tried some other things I found here but they are basically to do sums from the days prior. I'm just trying to grab the the prior day and day after values to add to the finished table. 

It seems like this should be possible, but I'm clueless....please help a fellow BI Dev! 

[Anonymous · Accepted solution]

Thank you for the help, sadly these soultions didn't give me the results I was looking for. I ended up just duplicating my orginal table (There were only 9 rows) and created a second slicer. So now they can do quick comparison for any days they want instead of just the day before and after. 

[v-lid-msft]

Hi @Anonymous ,

We can use the following measures to meet your requirement:

PreviousDay = 
VAR t =
    SELECTCOLUMNS ( ALL ( 'Table' ), "date", DATEVALUE ( [Date] ) )
VAR f =
    SELECTCOLUMNS ( FILTERS ( 'Table'[Date] ), "date-f", DATEVALUE ( [Date] ) )
RETURN
    MAXX ( FILTER ( t, [date] < MINX ( f, [date-f] ) ), [date] )

AfterDay = 
VAR t =
    SELECTCOLUMNS ( ALL ( 'Table' ), "date", DATEVALUE ( [Date] ) )
VAR f =
    SELECTCOLUMNS ( FILTERS ( 'Table'[Date] ), "date-f", DATEVALUE ( [Date] ) )
RETURN
    MINX ( FILTER ( t, [date] > MAXX ( f, [date-f] ) ), [date] )

If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

Have you looked at LOOKUPVALUE function? If you have the date, you should be able to add one or subtract one and then use LOOKUPVALUE to return the value for that day.