Formatting from Text-type to Date-type

[Niels_NL]

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

[Greg_Deckler]

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]

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.