Recent Data with date period

[pxg08680]

Hi everyone,

                    I have database with 2 columns, one is pricingdate and other is market fix rate. so the market fix rate changes from time to time. In my report I added a slicer with calendar dateperiod like last 1 month, 3 months and so on.

This is how it looks like. Now when I select last 6 months I want the result as 1.31147541 but when i select last 9 months i want my result to be 1.301998568.

How do I get it.

Thanks

[Phil_Seamark · Accepted solution]

Hi @pxg08680

I think I understand what you are after.

I created two tables.  The first table called dates just carries a single column of dates  using this code

Dates = CALENDAR(Date(2010,1,1),TODAY())

The second I hard coded in these three rows (2 columns) , and used this to drive my slicer.

Finally I created the following measure

Measure = 
var SlicerValue  = MAX('Slicer Table'[SlicerID])
var LastDateMonth = LASTDATE(DATEADD('Dates'[Date],-SlicerValue,MONTH))
var LastActualMonth = CALCULATE(LASTDATE('Raw Data'[Date]),'Raw Data'[Date] < LastDateMonth)
RETURN CALCULATE(MAX('Raw Data'[MSPricingFXRate]),'Raw Data'[Date] = LastActualMonth)

I created no relationships between any of the tables. 

The idea is that when you make a selection on the slicer, the ID column controls how many months you want to jump back to.  Then the relevant MSPricingFXRate is returned prior to that jump back in time.