Irregular Behaviour DAX

[Anonymous]

Hello,

I am using the DAX to filter a valid date which is greater than today's date. I see it misbehaving in the following example

I am using the DAX below,

IsValidNow =

VAR VALID_TO = FORMAT(Pricelist_ADLS[Valid To],"dd-mmm-yyyy")

VAR TODAY = FORMAT(NOW(),"dd-mmm-yy")

RETURN

IF(VALID_TO >=TODAY,"Yes","No")

valid To                is valid

31-jan-2018           yes

06-feb-2018           no

31-dec-9999          yes

Is it the type error??...valid to is a date field.

Thanks for the help in advance!

[BekahLoSurdo · Accepted solution]

Hi @Anonymous,

When I recreated this, the columns were type Text so I tried this:

IsValidNow = 
VAR _VALID_TO = DATEVALUE(Pricelist_ADLS[Valid To])
VAR _TODAY = TODAY()
RETURN
IF(_VALID_TO >=_TODAY,"Yes","No")

If the format was important to you for other reasons, you can wrap your original FORMAT() in a DATEVALUE() - I tried that as well and it works (but this is simpler).

[Anonymous]

@Anonymous  and @BekahLoSurdo ...Thanks for the solutions...The reason I need a format as there was a problem while I share the dashboard to a European customer.Date format issues(dd/mm/yy for European customer and mm/dd/yy for me)I believe they are due to regional options within power bi.

@BekahLoSurdo , As you said...I have made the change like below,

IsValidNow =

VAR _VALID_TO = DATEVALUE(FORMAT(Pricelist_ADLS[Valid To],"dd-mmm-yy"))

VAR _TODAY = DATEVALUE(FORMAT(TODAY(),"dd-mmm-yy"))

RETURN

IF(_VALID_TO >=_TODAY,"Yes","No")

but for some reason, it is still interpreted as a string but not the date value.

[Anonymous]

@Anonymous 

Do you have the option to use Power Query at all?  If so, you can set the locale of Date/Time:

[Anonymous]

@Anonymous, Thanks for the reply.

I am using the power query to fix the date locale to Europe but the issue occurs when I do a compare today with valid to on the Dax.

I am using the locale to convert the string format 23.5.2019 of valid to(date format of Europe) to date and then to compare it with today(today in format 5/23/2019 vs valid to in 23/5/2019). So, I am trying to convert both to the format "dd-mm-yy" to make them always consistent. but am unable to achieve it.

But I assume when European customer tries to view the report it will be consistent to him. If I let the way it is as default( [valid to] >= TODAY()).

[Stachu]

I'd suggest comparing using date column without any hardcoded formats - that way you effectively compare integers which should always give the same outcome.
I'd only use formatting for the final presentation after all the comparisons

[Anonymous]

Thanks For the suggestions, As expected the Dashboard looks perfect for European customer as both the date format match for them(I left the default values of dates, no hardcoding). 

Thanks.

[Anonymous · Accepted solution]

Think the issue here is when you format the date it changes the data type to text. There's no need to format it though, you can just use 

Column = 
if( Table2[Valid To] >= Now(), "Yes", "No")

[Stachu]

your variable name is the same as the function name, the other reason may be that NOW() is also returning time, also the formats are not consistent
try this

IsValidNow =
VAR _VALID_TO = FORMAT(Pricelist_ADLS[Valid To],"dd-mmm-yyyy")
VAR _TODAY = FORMAT(TODAY(),"dd-mmm-yyyy")
RETURN
IF(_VALID_TO >=_TODAY,"Yes","No")

[Anonymous]

@Anonymous  , Unfortunately, it didn't solve my issue. It's still the same.

Thanks.

[BekahLoSurdo · Accepted solution]

Hi @Anonymous,

When I recreated this, the columns were type Text so I tried this:

IsValidNow = 
VAR _VALID_TO = DATEVALUE(Pricelist_ADLS[Valid To])
VAR _TODAY = TODAY()
RETURN
IF(_VALID_TO >=_TODAY,"Yes","No")

If the format was important to you for other reasons, you can wrap your original FORMAT() in a DATEVALUE() - I tried that as well and it works (but this is simpler).