topic Re: Creating a new column based on date differences and matching values in DAX Commands and Tips

Creating a new column based on date differences and matching values

mrfsca — Thu, 25 May 2023 03:45:30 GMT

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?

Re: Creating a new column based on date differences and matching values

Mrxiang — Thu, 25 May 2023 03:48:58 GMT

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.

Re: Creating a new column based on date differences and matching values

mrfsca — Thu, 25 May 2023 13:35:00 GMT

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.