Forum Discussion
Power BI MIN Date Between Two Dates
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
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?
7 Replies
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?
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
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.
Hi,
Do you want this as a calculated column or as a measure?
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
