Forum Discussion

chrismpbi's avatar
chrismpbi
Frequent Visitor
8 years ago

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 dat...
Need Help
ChrisMendoza's avatar
ChrisMendoza
Resident Rockstar
8 years ago

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's avatar
    chrismpbi
    Frequent Visitor
    8 years ago

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