Forum Discussion
SUM Measure based on a created date
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
3 Replies
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.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.
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
