Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
@Anonymous - 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/991790#M436
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.
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
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |