Forum Discussion

pxg08680's avatar
pxg08680
Icon for Resolver III rankResolver III
9 years ago
Solved

Recent Data with date period

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...
Need Help
Show and Tell
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    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.

     

Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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.