Forum Discussion
Set an IF for different dates
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 |
HI Anonymous ,
NEW_COLUMN = IF('Table'[date] < DATE(2020,5,5), 'Table'[column1], 'Table'[column2])
5 Replies
- amitchandak
Super User
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]))
camargos88Community Champion
HI Anonymous ,
NEW_COLUMN = 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
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
