Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
Hi
I have two SQL queries from different databases.
Database A contains hourly price data
Database B contains daily price data
I need to create a chart showing the total hourly price by multiplying the hourly price with the daily price. So the daily price effectively needs to be repeated 24 times a day so that it can be multiplied to the hourly price.
I have a calendartable but I can't figure out how to join them but to be able to use hourly granularity in tables and charts.
thanks very much in advance
DATABASE A - hourly price
DATETIME VALUE
01/01/2017 00:00:00 45.45
01/01/2017 01:00:00 34.56
01/01/2017 02:00:00 43.34
DATABASE B - daily price
DATETIME VALUE
01/01/2017 00:00:00 15.67
02/01/2017 00:00:00 13.56
Hi @Anonymous
I think I follow what you need.
What I did was add a new column to the HourlyPrice table that only contained the date. I did this using the Add Date column in the query editor
This allowed me to create a relationship between the HourlyPrice and DailyPrice tables based on my new HourlyDate.Date column and the date column already in the Daily table
With this in place I added this calculated measure to the Daily table
Daily Price = CALCULATE(SUM(Daily[Value]))
Which I then used in a new calculated column on the Hourly table
Hour x Daily = CALCULATE('Daily'[Daily Price]) *'HourlyPrice'[Value]
This is the result based on your sample data.
Are we getting close?
Hi Phil
Thanks for the quick reply. I think it's almost there but it's not quite getting to where I wanted. Perhaps my example was too simple so wasn't quite covering what was needed
I've uploaded some data here over three tabs
https://www.dropbox.com/s/wusqsxeyt3tyeh2/powerBImixeddates.xlsx?dl=0
What I need to end up with an hourly data series to create a spark spread ( the theoretical profit of running a power plant in case you are interested!)
The formula is: HourlyPowerPrice - ((DailyGas[VALUE] + DailyCarbon[VALUE]*0.202)/0.55
I tried to apply your suggestions but am hitting a problem. I managed to relate the charts via the date as suggested.
The daily values are different for each day but what I am getting is then an average of the daily values over the whole time series rather than a distinct price for each day.
Does that make sense?
I am very grateful for any pointers
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.