Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Irregular Behaviour DAX

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!

 

Need Help
  • BekahLoSurdo's avatar
    BekahLoSurdo
    7 years ago

    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's avatar
    Anonymous
    7 years ago

    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")

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
    Stachu
    Community Champion
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

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

       

      Thanks.

      • BekahLoSurdo's avatar
        BekahLoSurdo
        Resolver IV
        7 years ago

        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's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      Anonymous 

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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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()).