Text To Date randomly switches days and months

[yossi]

I have columns that are looked up from different source, so I had to convert them into text in order to be able to look them up. Then when they were looked up I did new column with text to date using "dd/mm/yyyy". But it randomly uses format mm/dd and dd/mm.

I want to have them all as "dd/mm/yyyy". I am lost.

[Ahmedx]

Column = VAR _patch =SUBSTITUTE([Date],"/","|")
         VAR _Result =
          DATE(PATHITEM(_patch,3),PATHITEM(_patch,2),PATHITEM(_patch,1))
RETURN
if ( NOT ISBLANK([Date]),_Result,0)

[Ahmedx · Accepted solution]

[yossi]

Thank you for your time and helping me, Ahmad. I was able also to solve it with quicker (or easier) formula:

Submission = IF(

    Source[Submission TEXT] = "",0,

    DATE(

        RIGHT(Source[Submission TEXT],4),

        MID(Source[Submission TEXT],4,2),

        LEFT(Source[Submission TEXT],2)))

[yossi]

[Ahmedx]

[Ahmedx]

[yossi]

But how do I do that in Dax? Lookup is done in Table tab in Dax, so I don't have these columns in Power Query.

To convert I used this formula:

Submission Date = IF(

    Source[Submission TEXT] = "",0, DATEVALUE(FORMAT(Source[Submission TEXT], "DD/MM/YYYY")))