SUM Measure based on a created date

[Serdet]

Hi,

I have two datasets

Dataset 1

Date	ID	ID Type	Estimate Hours	Hours Used
01/01/2023	1	Erect	10	10
02/01/2023	1	Dismantle	5	5

Dataset 2

Created Date	ID	Actual Hours Erect	Actual Hours Dismantle
01/01/2023	1	11	6
02/01/2023	1	11	6

Dataset 1 & 2 have a relationship between the ID column. 

I have two DAX measures

Dax measure 1

Calculates the difference in hours from 'Estimate Hours' in Dataset 1 and 'Actual Hours Erect' or 'Actual Hours Dismantle in Dataset 2' depending on the job type given in Dataset 1. 

DAX measure 2 (this is shown on a graph visual that plots points on a monthly summary basis)

Calculates producitivty - SUM(dataset1,Estimate Hours) / SUM(dataset1, Hours Used)

I need to create a third visual that plots points on a monthly summary basis. 

The logic required is as follows: 

IF 'Created Date' in Dataset 2 falls in date timeline on X axis, SUM difference (DAX measure 1) for related 'ID'

This should return a plus or minus figure each month if the ID has a created date within that specifc month.

Many thanks,

Elliot

[rusgesig]

The sample data you provided wasn't great to work with since the 2 tables you refer to as Dataset 1 and 2 do not have primary keys. I'm still not sure what exactly you want to visualise based on your descriptions, but if you have 2 tables you'd want to join their ID's (preferably 1:1, 1:many if you must, NEVER many:many- which is what you have to do here) and join the date columns to a Calendar. You can inactive relationships in measures.
I even made a sample DS for you, wasn't that nice? Uploaded here since I can't add pbix files https://files.catbox.moe/you93s.pbix

[rusgesig]

Are you using a Calendar table? You need a Calendar connected to both those date columns if you want to display the two measures on a shared x-axis.

Can you provide a screencap of your data model? It's hard to assume when I don't know what your tables look like.

[Serdet]

Hi, 

Thanks for your quick response.

I have attached an example here https://files.catbox.moe/vqr77w.pbix 

This shows example data, existing calculated column and difference measure.

I need to be able to show the difference on the given timeline but only if the 'Created Date' falls in line with the date in Dataset 1. 

For example, if a difference of 10 was shown using the difference measure but its 'Created Date' was 2 months ago. This difference would need to be applied to that date instead of the date in dataset 1.

Hope this helps.