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 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

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.

     

1 Reply

  • 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.