Forum Discussion

Niels_NL's avatar
Niels_NL
Advocate I
10 years ago

Formatting from Text-type to Date-type

Hello all,

 

I am creating a new column that gives a True/False output based on whether a particular column contains particular values.

Situation: I want to know whether a customer purchased something in the February 2016. If he did: return TRUE, if he didn't: return FALSE.

 

I am using the following function as input for my column:

Column1 = AND(
  CONTAINS(tbl_Revenue2016;tbl_Revenue2016[Customer];tbl_Customers[Customer];
  CONTAINS(tbl_Revenue2016;tbl_Revenue[Date];"1-2-2016")
)

The values in "tbl_Revenue[Date]" are all dates in this format: " d-M-yyyy ". If I manually change the datatype of these values to " Text ", the DAX formula does work, so it's not a formula-error.

But I don't want these dates in revenue-table to become text-types, so my only solution is to convert my value-parameter in the contains-function to a date-type.

 

So how can I format the value-parameter in the contains-function to a date-type (preferably with " d-M-yyyy ")?

 

I've tried doing both format-functions below, but these didn't work:

FORMAT("1-2-2016";"d-M-yyyy")
FORMAT("1-2-2016";"d/M/yyyy")

 

Any help? :)

 

Best regards,

Niels

Need Help

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    10 years ago

    Not sure you can get there with CONTAINS using a date. Have only ever used CONTAINS with numeric and text values. You might try changing your formula to a CALCULATE using COUNT, COUTA or COUNTROWS with a FILTER statement to replace your CONTAIN statements. Oh, and wrap it all in an IF statement to get you to your true/false value.

    • Niels_NL's avatar
      Niels_NL
      Advocate I
      10 years ago

      Well, I figured out a different way while still using the CONTAINS function.

       

      Instead, I used the DATE function to properly convert dates to the date-type:

       

      Column1 = AND(
  CONTAINS(tbl_Revenue2016;tbl_Revenue2016[Customer];tbl_Customers[Customer];
  CONTAINS(tbl_Revenue2016;tbl_Revenue[Date];DATE(2016;2;1))
)

      This seems to work as well, as I am not getting an errormessage anymore.

      Of course, with an IF statement around it to be able to get a True/False output, as you said.