cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pxg08680 Member
Member

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.

Capture.PNGThis 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

1 ACCEPTED SOLUTION

Accepted Solutions
Phil_Seamark Super Contributor
Super Contributor

Re: Recent Data with date period

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.

 

slicer2.jpg

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.

 

slicer1.jpg


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

1 REPLY 1
Phil_Seamark Super Contributor
Super Contributor

Re: Recent Data with date period

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.

 

slicer2.jpg

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.

 

slicer1.jpg


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 176 members 1,783 guests
Please welcome our newest community members: