Forum Discussion
Linear Interpolation between dates and another dimension
Hi Everyone, Im trying to replicate this report in Power BI http://ir.eia.gov/ngs/ngs.html I found the raw data for free via Odata which is great as I can auto refresh Data Source...
I try Greg_Deckler
Maybe the way they are calculating it its different?
http://ir.eia.gov/ngs/methodology.html
Interpolated Value =
VAR x3 = MAX('Date'[Date])
VAR match = CALCULATE(MAX('Working Gas in Underground Storage (W)'[VALUE]),FILTER('Working Gas in Underground Storage (W)','Working Gas in Underground Storage (W)'[DATE]=x3))
VAR x1 = CALCULATE(MAX('Working Gas in Underground Storage (W)'[DATE]),FILTER('Working Gas in Underground Storage (W)', 'Working Gas in Underground Storage (W)'[DATE]<=x3))
VAR x2 = CALCULATE(MIN('Working Gas in Underground Storage (W)'[DATE]),FILTER('Working Gas in Underground Storage (W)','Working Gas in Underground Storage (W)'[DATE]>=x3))
VAR y1 = CALCULATE(MAX('Working Gas in Underground Storage (W)'[VALUE]),FILTER('Working Gas in Underground Storage (W)','Working Gas in Underground Storage (W)'[DATE]<=x3))
VAR y2 = CALCULATE(MIN('Working Gas in Underground Storage (W)'[VALUE]),FILTER('Working Gas in Underground Storage (W)','Working Gas in Underground Storage (W)'[DATE]>=x3))
RETURN IF(NOT(ISBLANK(match)),match,y1 + (x3 - x1) * (y2 - y1)/(x2 - x1))
Anonymous - I'll have to find some focus time to dig into this, it is probably going to take at least a few hours of work to puzzle it all out.
Greg_Deckler Thank you! I have been playing around with it and no luck.