Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |