topic Re: DAX for date column in DAX Commands and Tips

DAX for date column

EmilyM2019 — Mon, 02 Mar 2020 11:25:44 GMT

Hi all,

I am stuck with a DAX if someone can help me.

I have a table with 2 date columns, Column A and Column B.

I want a new column (Column C) that will look and see if there is a value in Column A, if it is blank it picks up the value from Column B. If Column A isn't blank then it populates Column C with that value. As per the table below.

TIA, Emily.

Column A	Column B	Column C
	03/02/2020	03/02/2020
02/01/2020	21/01/2020	02/01/2020
13/01/2020	01/02/2020	13/01/2020
19/01/2020	03/02/2020	19/01/2020
24/12/2019	31/12/2019	24/12/2019
	15/12/2019	15/12/2019
	30/12/2020	30/12/2020

Re: DAX for date column

Anonymous — Mon, 02 Mar 2020 11:43:19 GMT

Hi EmilyM2019 ,

Try the following:

Date = if(isblank('Table'[ColumnA]);'Table'[ColumnB];'Table'[ColumnA])

Re: DAX for date column

ibarrau — Mon, 02 Mar 2020 11:49:59 GMT

Hi there. You should consider do this in Power Query over the query editor. If you want DAX use the first answer, if you want power query try adding a custom column like this:

if [ColumnA] = "" then [ColumnB] else [ColumnA]

Just ask if columnA is blank and you can get the true false with the columns.

Hope this helps,

Regards

Re: DAX for date column

EmilyM2019 — Mon, 02 Mar 2020 12:32:18 GMT

Hi,

Thanks for coming back to me. I'm now trying to do this in Power query but it hasn't worked. Using the add custom column option I have done:

= Table.AddColumn(#"Renamed Columns2", "Custom", each if[Column A]=""then[Column B]else[Column A])

and it isn't working. The only value that is returning in the new custom column is the value of column A.

Any ideas?

Re: DAX for date column

ibarrau — Mon, 02 Mar 2020 12:35:55 GMT

I'm sorry I forget to add the null cases too. Try this:

if [ColumnA] = "" or [ColumnA] = null then [ColumnB] else [ColumnA]

Regards,

Re: DAX for date column

EmilyM2019 — Mon, 02 Mar 2020 12:52:05 GMT

Thank you, this has worked, hurrah.

Just to build on this how would I do:

If column A and column B are blank then return todays date?

Re: DAX for date column

ibarrau — Mon, 02 Mar 2020 13:12:13 GMT

Well it should be similar

if [ColumnA] = "" or [ColumnA] = null then if [ColumnB] = "" or [ColumnB] = null then DateTime.LocalNow() else [ColumnB] else [ColumnA]

You can continue adding if statements in the "then" or "else" in case you need them.

Regards,

Re: DAX for date column

EmilyM2019 — Mon, 02 Mar 2020 15:57:58 GMT

Thanks for your help.