Forum Discussion
Help - joining tables by date
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!
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
1 Reply
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