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...
Hi,
Thank you for your quick response, this is the sample data
and this is the pibx file
I guess my where I am confused is would I have to create a date table with each day of year repeating itself each occurence of the dimension to showcase daiyly volumes per region?
Actually might be best to use the pbix file as the data from the sample file is pivoted. Greg_Deckler
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))- Greg_Deckler
Community Champion
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.
