Skip to main content
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Not applicable

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


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!

Not applicable

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

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors