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

Joining dates at different granularities

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

 

2 REPLIES 2
Phil_Seamark
Microsoft
Microsoft

Hi @elegreen

 

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

 

Add Date Col.png

 

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

 

Date.png

 

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.

 

Hourly.png

 

Are we getting close?

 

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

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.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.