Power BI MIN Date Between Two Dates

[bbotros77]

I was trying to get the Min date between two dates Columns, it works fine only if the two dates are available... if only one date is available, it comes up blank (it is supposed to pick the one date) . However if i changed to MAX instead , it works just fine even if one date only is available

Here is the DAX Formula I use

Date First Entered ATI or MCA = MIN('Application'[Date First Entered ATI], ('Application'[Date First Entered MCA].[Date]))

Appreciate any advice on what I may be doing wrong

Thanks

[Ashish_Mathur]

Hi,

Do you want this as a calculated column or as a measure?

[bbotros77]

Hi Ashish,  I already have the Two Dates available ( I am not sure if they are considered column or measure , but each date has its own DAX formula ) so out of those two calculated dates , I am trying to get the MIN date

Apology If I was not clear enough as I am still learning about PBI 

[OwenAuger · Accepted solution]

Hi @bbotros77 

Yes, when MIN is used with two scalar arguments, BLANK can be returned if one argument is blank and the other argument is "greater than" BLANK (which is the case for values corresponding to positive numbers).

This is different from how blanks are treated with MIN (with a single column reference argument) or MINX.

You could rewrite your expression by constructing a single-column table containing the two scalar values and use MINX like this, which would return BLANK only if both dates are blank:

Date First Entered ATI or MCA =
MINX (
    { 'Application'[Date First Entered ATI], 'Application'[Date First Entered MCA] },
    [Value]
)

I think you can omit the .[Date] as well.

Does this work?

[bbotros77]

Hi Owen , I will try to figure out how to create that Single column table and try your advice ( I am still learning Power BI) . Will kep you posted if it works

Thanks

Bassem 

[OwenAuger]

@bbotros77 

Sure thing :). The code I posted with the curly braces table constructor is what I was referring to.

[bbotros77]

Got It & it Works. Thank you so much Owen!

[vicky_]

the comparison will treat a blank value as if it's a date as well (where the date is some time in the 1900s. or something like that), so between the date that you're trying to compare it with and a blank value, the MIN function will return a blank date. 
If you want to return the Minumum existing date, try wrap it in an if statement like so:

Date First Entered ATI or MCA = IF(
    ISBLANK('Application'[Date First Entered ATI]), 'Application'[Date First Entered MCA],
    IF(ISBLANK('Application'[Date First Entered MCA]), 'Application'[Date First Entered ATI]), 
    MIN('Application'[Date First Entered ATI], ('Application'[Date First Entered MCA].[Date]))

(NOTE: i haven't tested that code, it's highly likely i missed a bracket somewhere but I hope you get the point.