Finding the price of an item on a specific date - No discrete date for change/Using date range

[AndyB1]

Hi Folks, 

Fairly new to PbI, 

I want to create a measure for the price of a product a) in the past week  b) in the past month.

My data is organised with multiple products and multiple rows of data for each product which detail price changes effective from upon till. 

Product	Created Date	Amount	SystemModStamp
A	17/10/2020	$    10.00	23/11/2020
A	23/11/2020	$    12.00	05/12/2020
A	05/12/2020	$    11.00	10/02/2021
A	10/02/2021	$    15.00	19/04/2021
A	19/04/2021	$    13.00	*Todays date function*

Is there an easy measure you would recommend? for instance I want to know what the price would be 1 month ago... this would obviosuly be 15.

I've played around with dateAdd and datesbetween but had no luck so far. 

Thanks,

[Greg_Deckler · Accepted solution]

@AndyB1 I would stay away from DATEADD personally, https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

For a month ago, I would do something along the lines of:

Price a month ago Measure =
  VAR __Today = TODAY()
  VAR __Product = MAX('Table'[Product])
  VAR __EOMonthAgo = EOMONTH(__Today,-1)
  VAR __MonthAgoDate = DATE(YEAR(__EOMonthAgo),MONTH(__EOMonthAgo),DAY(__Today)
  VAR __Date = MAXX(FILTER('Table',[Created Date] < __MonthAgoDate && [Product] = __Product),[Created Date])
RETURN
  MAXX(FILTER('Table',[Product] = __Product && [Created Date] = __Date),[Amount])

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...