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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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

 

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!

Anonymous
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

 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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