Grab the smallest date from today

[newkingdom]

	Today's date	1/15/20
Id	Date A	Date B		New Column
123	1/1/20	1/4/20		1/1/20
12331	1/2/20	1/29/20		1/2/20
12353	null	2/23/20		1/29/20
123123	null	3/19/20		1/29/20
4564	1/10/20	4/13/20		1/10/20
5464	null	5/8/20		1/29/20
456456	2/1/20	6/2/20		2/1/20
45645	null	6/27/20		1/29/20

Hi, I need to create a new column which will use the date from Date A column and if it is null then grab the smallest date from Date B which is greater that today's today

[v-lili6-msft · Accepted solution]

Hi @newkingdom @amitchandak 

There is a mistake in the formula of amitchandak provided. 

It just needs a little modification as below:

if(isblank(table[column B]),(minx(filter(table,table[column B] >Today()),table[column B])),table[column A])

if(isblank(table[column A]),(minx(filter(table,table[column B] >Today()),table[column B])),table[column A])

or use this formula:

IF (
    ISBLANK ( 'table'[Date A] ),
    CALCULATE ( MIN ( 'table'[Date B] ), FILTER ( 'table', 'table'[Date B] >= TODAY () )),
    'table'[Date A]
)

Regards,

Lin

[amitchandak]

Create it as a new column

if(isblank(table[column B]),(minx(filter(table,table[column B] >Today()),table[column B])),table[column A])

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601