Help - joining tables by date

[acarmichael]

I have two tables, one with number of DL hours worked per day (DD/MM/YY) and one with information for each batch of product completed (Batch ID, Completion date (DD/MM/YY), GalYield).   

I want to be able to make a summary chart that calculates the Gallons/Hours for each month but I cannot figure out how to effectively merge/aggregate the tables.  The hours worked table has a unique entry for each day, the Batch table has a unique entry for each batch and could include multiple for each day. 

I have tried creating a new table using the Summarize function but cannot get it to join and summarize in one table. 

Any tips would be appreciated.  Thank you!

[TeigeGao · Accepted solution]

Hi acarmichael,

Could you please share some sample data to us for analysis?

Based on my understanding is that you want to calculate the Gallons/Hours for each month, the Gallons for each batch of product is based on the complete date.

In this scenario, we can first create a relationship between the two tables using the date, if the DL hours worked per day table has duplicate date, we can create a many to many relationship, else create a one to many relationship.

Then we can create a measure like below:

Gallons/Hours = DIVIDE(SUM('batch of product'[GalYield]),SUM('DL hours'[DL hours ]))

Then we can drag the measure and date to the visual, let date display month:

Best Regards,

Teige