Forum Discussion

myti's avatar
myti
Helper II
9 years ago

Date format problem

Dear Experts,   I have a calculated Measure which is  an output in Date format like 1/1/2017. Once I want to use this output in if condition I can not get the correct answer and "if condition" can ...
Need Help
Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
9 years ago

Can you please add 3 test columns to your table (to be deleted later)

 

My Year Col = YEAR([Measure])

My Month Col = MONTH([Measure])

My Day Col = DAY([Measure])

Just to see if these values are what you would expect

parry2k's avatar
parry2k
Super User
9 years ago

myti you mentioned it is a calculated measures which is date type?

 

What formula you are using for this calculated measures? 

What is your data source?

What is data type of this new measure?

 

Also as Phil_Seamark mentioned, create those fields to see what value you are getting?

 

Can you also try this following, assuming your locale setting for date is MM/DD/YYYY, just wondering if your measure is text type and following formula will convert date to text for comparison purpose.

 

CalculatedColumn=IF([Measure]=FORMAT(DATE(2017,1,1), "MM/DD/YYYY"),1,0)

 

Although there are many unknown, once we have reply on above question, it will help to resolve the issue.

 

Thanks,

Parv

  • myti's avatar
    myti
    Helper II
    9 years ago

    Hi parry2k

     

    I receive the value for  calculated measure based on the below formula.It get the current value of page level filter which is based on the date format.

    Measure = If ( HasOneValue('landing-pages'[ipg:month]) , Values ('landing-pages'[ipg:month]))

     

     

    I tried your formulah but I recived error ("DAX comparison operations do not support comparing values of type Date with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.")

     

    Hopefully my information is good enough to find  a solution.

     

    Thank you,

    Myti

    • v-sihou-msft's avatar
      v-sihou-msft
      Microsoft Employee
      9 years ago

      myti

       

      Make sure your [Measure] returns date type. As I tested, it supposes to work with your original formula.

       

       

      Regards,

      • parry2k's avatar
        parry2k
        Super User
        9 years ago

        Try this:

         

        Measure = If ( HasOneValue('landing-pages'[ipg:month]) , Values ('landing-pages'[ipg:month]), BLANK())

CalculatedColumn=IF([Measure]=BLANK() ||[Measure]<>DATE(2017,1,1),0,1 )

        Assuming ipg:month is data type, as v-sihou-msft suggested, make sure your measure data type is also Date