Dax Date Comparison

[chrismpbi]

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”.

[chrismpbi]

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”.

[ChrisMendoza]

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?

[chrismpbi]

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".