Microsoft Dataverse and date-type-columns

(0) Share

Report

Posted on by Linn93

I have four different date-type (date only) columns:

DateColumn1

DateColumn2

DateColumn3

DateColumn4

I need to retrieve the earliest date from the four different dateColumns in a new column.
How can I write a formula in Dataverse to be able to get the earliest date out of the four date-columns in a new column?
I have attempted to use Min function in a Power fx column type, but I get a error saying it is not possible to use the Min function with date-type columns.
How can I get the earliest date displayed in a new column in a Dataverse table?

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (3)

Answers (0)

MarioRing 541 Super User 2024 Season 1 on at

Like (1)

Report
Here you are, it should be something like this:
(I tested this, and it worked!)

And here is the formula for easy copying - change my DateXs into your field names:
If(If(DateA<DateB,DateA,DateB)<(If(DateC<DateD,DateC,DateD)),(If(DateA<DateB,DateA,DateB)),(If(DateC<DateD,DateC,DateD)))

Was this reply helpful? Yes No
Linn93 62 on at

Like (0)

Report
Thank you @MarioRing. Appreciate your help!

I need the IF statement to take into consideration that all the columns does not always have values. The IF statement still needs to return the lowest/ earlies date based on the columns that has a value.
ChatGPT suggested the following IF-statement:

=IF( AND(ISBLANK(DateA), ISBLANK(DateB), ISBLANK(DateC), ISBLANK(DateD)), "", // All values are blank, return blank IF( AND(ISBLANK(DateA), ISBLANK(DateB)), IF(ISBLANK(DateC), IF(ISBLANK(DateD), "", DateD), IF(ISBLANK(DateD), DateC, IF(DateC < DateD, DateC, DateD))), IF( AND(ISBLANK(DateC), ISBLANK(DateD)), IF(ISBLANK(DateA), IF(ISBLANK(DateB), "", DateB), IF(ISBLANK(DateB), DateA, IF(DateA < DateB, DateA, DateB))), IF( AND(ISBLANK(DateB), ISBLANK(DateC)), IF(ISBLANK(DateA), IF(ISBLANK(DateD), "", DateD), IF(ISBLANK(DateD), DateA, IF(DateA < DateD, DateA, DateD))), IF( AND(ISBLANK(DateA), ISBLANK(DateD)), IF(ISBLANK(DateB), IF(ISBLANK(DateC), "", DateC), IF(ISBLANK(DateC), DateB, IF(DateB < DateC, DateB, DateC))), IF( AND(ISBLANK(DateA), ISBLANK(DateC)), IF(ISBLANK(DateB), IF(ISBLANK(DateD), "", DateD), IF(ISBLANK(DateD), DateB, IF(DateB < DateD, DateB, DateD))), IF( AND(ISBLANK(DateB), ISBLANK(DateD)), IF(ISBLANK(DateA), IF(ISBLANK(DateC), "", DateC), IF(ISBLANK(DateC), DateA, IF(DateA < DateC, DateA, DateC))), IF( ISBLANK(DateA), IF(DateB < DateC, IF(DateB < DateD, DateB, DateD), IF(DateC < DateD, DateC, DateD)), IF( ISBLANK(DateB), IF(DateA < DateC, IF(DateA < DateD, DateA, DateD), IF(DateC < DateD, DateC, DateD)), IF( ISBLANK(DateC), IF(DateA < DateB, IF(DateA < DateD, DateA, DateD), IF(DateB < DateD, DateB, DateD)), IF( ISBLANK(DateD), IF(DateA < DateB, IF(DateA < DateC, DateA, DateC), IF(DateB < DateC, DateB, DateC)), IF( DateA < DateB, IF(DateA < DateC, IF(DateA < DateD, DateA, DateD), IF(DateC < DateD, DateC, DateD)), IF( DateB < DateC, IF(DateB < DateD, DateB, DateD), IF(DateC < DateD, DateC, DateD) ) ) ) ) ) ) ) ) ) ) ) ) )
The problem is that the Dataverse Power fx Formula-column cannot be longer than 1000 characters.

Was this reply helpful? Yes No
MarioRing 541 Super User 2024 Season 1 on at

Like (0)

Report

Write custom plugin or create power automate flow then:(

Was this reply helpful? Yes No