cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

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: http://eiaconnector.oilstat.com/odata/naturalgas

Table: Storage - Weekly Working Gas in Underground Storage

 

As I started creating the measures, I noticed they dont match with the report on the website ( 5 year average, yearly change, etc) 

 

As I started looking at the report methodology, I noticed they are interpolaring the weekly values into daily to calculate those yearly and 5 year averages.

 

http://ir.eia.gov/ngs/methodology.html

 

I started investigating if this is something I can do in Power Bi and found this amazing post

 

Kudos to Greg_Deckler

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Linear-Interpolation/m-p/330712

 

However when I try to apply it here, Im stuck because this report has different dimensions. I have weekly volumes by region that i need to calculate daiyly

 

Can I calculate the dayly values by interpolating by region if I cant relate the table? 

 

Any help would be highly appreciated it

6 REPLIES 6
Super User IV
Super User IV

@reynags91 - Glad you liked that post. So, it seems that you are running into an issue because you need this to be dynamic (measure) and not calculated columns?

 

If you could maybe post some sample data and expected output that would be very helpful. 

 

Also, I did another similar post here: https://community.powerbi.com/t5/Quick-Measures-Gallery/Mind-the-Gap-Irregular-Time-Series/m-p/99179...

 

I do not see why this kind of thing couldn't be done "on the fly", essentially constructing the interpolation table as a table variable in DAX but I'd have to understand exactly what your expectations are.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi, 

 

Thank you for your quick response, this is the sample data

https://we.tl/t-Gyr0Z51QH3

and this is the pibx file

https://we.tl/t-kU8RumZUSh

 

 

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

 

Capture1.PNG

 

Maybe the way they are calculating it its different? 

 

http://ir.eia.gov/ngs/methodology.html

 

methodology.PNG

 

This is my DAX.

 

Am I doing something wrong?

 

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

@reynags91 - 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.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@Greg_Deckler  Thank you! I have been playing around with it and no luck.

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors