cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Creating a new column based on date differences and matching values

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?

1 ACCEPTED SOLUTION
Helper II
``````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],
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.``````
2 REPLIES 2
Frequent Visitor

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.

Helper II
``````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],
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.``````

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors