Showing results for 
Search instead for 
Did you mean: 
Helper I
Helper I

Joining dates at different granularities



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




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?




To learn more about DAX visit :

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


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


2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.