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

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.

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

Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City


Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.