Forum Discussion
Dax Date Comparison
Hi,
I hope someone is able to help me with this:
I have a table with data from different batch dates and would like to compare the planned date versus the actual date at different batch dates.
ID | Batch Date | Actual Date | Planned Date |
123214 | 04.05.2018 |
| 07.07.2018 |
43344 | 04.05.2018 |
| 08.07.2018 |
4545 | 04.05.2018 |
| 09.07.2018 |
123214 | 06.05.2018 |
| 11.07.2018 |
43344 | 06.05.2018 | 05.07.2018 |
|
4545 | 06.05.2018 | 03.07.2018 |
|
123214 | 07.05.2018 | 06.07.2018 |
|
43344 | 07.05.2018 | 05.07.2018 |
|
4545 | 07.05.2018 | 03.07.2018 |
|
For Example:
Planned date at Batch date 04.05. compared with Actual date at Batch date 07.05.:
| Planned at 04.05.Batch Date | Actual at 07.05.Batch | Days difference |
123214 | 07.07.2018 | 06.07.2018 | -1 |
43344 | 08.07.2018 | 05.07.2018 | -3 |
4545 | 09.07.2018 | 03.07.2018 | -6 |
Total |
|
| -10 |
I have tried using two different date tables (Previous and Current Date) that have an inactive connection to the batch date column but I can’t create correct measures for calculating the “days difference”.
2 Replies
Hello chrismpbi,
I decided to make a go at this. Hopefully it will get you close(r) to your desired result.
Using your sample dataset 'Table1' in DAX:
Create a new table:
tblActual = SUMMARIZECOLUMNS(Table1[ID],Table1[Actual Date],LASTDATE(Table1[Batch Date]))
Create another table:
tblPlanned = SUMMARIZECOLUMNS(Table1[ID],Table1[Planned Date],FIRSTDATE(Table1[Batch Date]))
In 'tblPlanned' add column:
Actual at 07.05.Batch = LOOKUPVALUE('tblActual'[Actual Date],'tblActual'[ID],'tblPlanned'[ID])In 'tblPlanned' add another column for [Days difference]:
Days difference = 'tblPlanned'[Actual at 07.05.Batch].[Day]-'tblPlanned'[Planned Date 04.05.Batch Date].[Day]
This should yield:
I honestly don't know how how this will work with additional data rows however, it seems to work for the small sample dataset.
Possibly there is a better solution to handling this?
Hi ChrisMendoza,
thank you very much for your response. I think as you mentioned this solution would work for this example data set. But my real dataset is much bigger with hundreds of "Batch Dates".
