cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
mrfsca
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? 

mrfsca_0-1684986208855.png

mrfsca_1-1684986232666.pngmrfsca_2-1684986253444.png

 

 

1 ACCEPTED SOLUTION
Mrxiang
Helper II
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],
    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.

View solution in original post

2 REPLIES 2
mrfsca
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.

 

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

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors