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 = FO...
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")
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()).

    • Stachu's avatar
      Stachu
      Community Champion
      7 years ago

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

        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.