Showing results for 
Search instead for 
Did you mean: 
elegreen Frequent Visitor
Frequent Visitor

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



Re: Joining dates at different granularities

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!

elegreen Frequent Visitor
Frequent Visitor

Re: Joining dates at different granularities

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

New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)