Set an IF for different dates

[Anonymous]

My table follows this structure:

date	column1	column2
02/01/2020	111	222
03/03/2020	222	777
07/10/2020	333	555
12/12/2020	444	999

I want to create a new column that, if the date is less than 05/05/2020, then the values will be taken from column1. If the date is greater than 05/05/2020, the value will be taken from column2.

The expected result would look like this:

date	column1	column2	new_column
02/01/2020	111	222	111
12/12/2020	222	777	777
07/10/2020	333	555	555
04/04/2020	444	999	444

[Anonymous]

@camargos88 
@Pragati11 
@amitchandak 
Thanks to everyone for the quick responses! Worked perfectly!

Just a small question before closing the topic: is it possible to insert Hour in this DATE function too? In case I wanted to get values ​​from a specific day and time?

[Pragati11]

Hi @Anonymous ,

There is quite interesting article on how you can use date and time functions in Power BI:

https://medium.com/@kishoripatil1823/date-and-time-dax-functions-in-power-bi-fd9155416178

Try checking it out and see how you can implement it in your scenario.

 There is a TIME function in Power BI which you can use. There is an existing thread for this. Check it:

https://community.powerbi.com/t5/Desktop/DAX-IF-statement-based-on-TIME/td-p/889249

Thanks,

Pragati

[amitchandak]

@Anonymous , Try like

new column = IF('Table'[date] < DATE(2020,5,5), 'Table'[column1], 'Table'[column2])

new measure = IF(max('Table'[date]) < DATE(2020,5,5), max('Table'[column1]), max('Table'[column2]))

new measure = sumx('Table', IF('Table'[date] < DATE(2020,5,5), 'Table'[column1], 'Table'[column2]))

[Pragati11]

HI @Anonymous ,

You can create a calculated column as follows:

NewValueCol = IF(Tablename[date] < DATE(2020, 05, 05), Tablename[Column 1], Tablename[Column 2])

Replace Tablename[date] in above DAX with your table and column.

Thanks,

Pragati

[camargos88 · Accepted solution]

HI @Anonymous ,

NEW_COLUMN = IF('Table'[date] < DATE(2020,5,5), 'Table'[column1], 'Table'[column2])