Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
NipawanV
Helper I
Helper I

Replace two blank date fields with another date column

Hello, I created column formula called "Date3" to replace with date field in the other columns.  Every line went well give the correct results but on line 6 I used "AND" & "BLANK() to check two date fields if both are blank, it should return modified date.  But it's empty.  Here is the formula:

NipawanV_0-1646235016636.png

 

Date3 =
SWITCH(
TRUE(),
[Due Date (scheduledend)] = BLANK(), [Actual End Date],
[Actual End Date] = BLANK(), [Due Date (scheduledend)],
AND([Due Date (scheduledend)], [Actual End Date]) = BLANK(), activitypointers[modifiedon],
[Actual End Date]
)

 

Could you kindly advise what wrong in the formula ? 

1 ACCEPTED SOLUTION

Hi @NipawanV ,

 

I have a test by your code, I and I could reproduce your problem. I think your issue should be caused that your conditional statement will go [Due Date (scheduledend)] = BLANK(), [Actual End Date] firstly, then it will return blank , there is something wrong in your And() function.

1.png

Try this code.

Date3 = 
IF (
    AND ( [Due Date (scheduledend)] = BLANK (), [Actual End Date] = BLANK () ),
    activitypointers[modifiedon],
    IF (
        [Due Date (scheduledend)] = BLANK (),
        [Actual End Date],
        IF (
            [Actual End Date] = BLANK (),
            [Due Date (scheduledend)],
            [Actual End Date]
        )
    )
)

Result is as below.

1.png

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
amitchandak
Super User
Super User

@NipawanV , Try like

coalesce([Due Date (scheduledend)], [Actual End Date],[Due Date (scheduledend)],activitypointers[modifiedon])

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

@amitchandak Many thanks but it doesn't work.  Apply the said line it doesn't show the result from line 7 as well. Line 7 in my formula if "Actual End Date" and "Activity Due Date " should pickup the result from "Actual End Date".  Now it's disappear.

See result in Date4 that applied your formula. (I used "created on" for the time being as it's the text field.  coalesce() doesn't allow me to use "modified on" which is the date field.) it doesn't return any date at all. 

Any more hints, please!! 

NipawanV_0-1646267602337.png

 




Hi @NipawanV ,

 

I have a test by your code, I and I could reproduce your problem. I think your issue should be caused that your conditional statement will go [Due Date (scheduledend)] = BLANK(), [Actual End Date] firstly, then it will return blank , there is something wrong in your And() function.

1.png

Try this code.

Date3 = 
IF (
    AND ( [Due Date (scheduledend)] = BLANK (), [Actual End Date] = BLANK () ),
    activitypointers[modifiedon],
    IF (
        [Due Date (scheduledend)] = BLANK (),
        [Actual End Date],
        IF (
            [Actual End Date] = BLANK (),
            [Due Date (scheduledend)],
            [Actual End Date]
        )
    )
)

Result is as below.

1.png

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello Rico, 

Many thanks indeed for the solution.  This is perfectly working well.  

With best regards,

Nipawan

Hello Rico,

many thanks for the revised code. i will run when i return to work next month 🙂

WBR, Nipawan

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.