Creating a new column based on date differences and matching values

[mrfsca]

I'm having difficulty creating a DAX logic to create a new column 'intransit'[exception_date], where the values are derived from the difference between 'intransit'[ETA CHN] and 'GRN'[Receive Date PPC]. They are connected through the [Invoice (PIB)] column. If the date in each row of 'intransit'[ETA CHN] is missing or different from the corresponding row in 'GRN'[Receive PPC], then automatically that date should be entered into the 'intransit'[exception_date] column. I have tried using the EXCEPT function, but it resulted in an error. Additionally, I want to create a new column to view 'intransit'[Invoice] based on 'intransit'[exception_date]. Can you please help me with this? 

[mrfsca]

Hi, thank you for the solution you provided. When I tried it, the column GRN[Received Date(PPC)] is not being recognized. There is a red underline below its name with the message "cannot find name" and a warning message saying "A single value for column [Received Date(PPC)] in table 'GRN' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation." Do you know which aggregation can be used? I tried using the MAX value, but it doesn't seem to give me the desired result.

[Mrxiang · Accepted solution]

Sure, I can help you with that. Here's the DAX logic to create the new column 'intransit'[exception_date]:

```
intransit[exception_date] =
IF(
    ISBLANK(intransit[ETA CHN]) || intransit[ETA CHN] <> GRN[Receive Date PPC],
    GRN[Receive Date PPC],
    BLANK()
)
```

This formula checks if the date in each row of 'intransit'[ETA CHN] is missing or different from the corresponding row in 'GRN'[Receive PPC]. If it is, then it enters that date into the 'intransit'[exception_date] column. Otherwise, it leaves the column blank.

To create a new column to view 'intransit'[Invoice] based on 'intransit'[exception_date], you can use the following formula:

```
intransit[Invoice Exception] =
IF(
    NOT(ISBLANK(intransit[exception_date])),
    intransit[Invoice],
    BLANK()
)
```

This formula checks if the 'intransit'[exception_date] column is not blank. If it is not, then it enters the corresponding 'intransit'[Invoice] value into the 'intransit'[Invoice Exception] column. Otherwise, it leaves the column blank.

I hope this helps! Let me know if you have any further questions.