Convert Text to Date and use in MIN

DAX DAX Calculations
1

OK, I have a column in Table1 called TheDate in the format dd/mm/yyyy which is stored as Text.
I need a DAX to convert that field to a Date format and use it in a MIN to get the first date in that field.
But DATEVALUE or CONVERT does not work…

2

Hi @marcster_uk

Suggest you should transform using power query to date and it will be very easy. But if you just want to get the min from the field then can use as below

MinDate = DATEVALUE( MIN('Table1'[TheDate] ) )

3

Hi @MK3010 , I would use Power Query, which I may have to do, but thought it would be possible with DAX. Your formula does not work. Example, TheDate holds:
01/12/2021
02/12/2021
03/12/2021
04/12/2021
30/11/2021
All stored as Text. I want the DAX to return 30/11/2021 but is returning 01/12/2021.
Weird?..

4

HI @marcster_uk

Ah I see, as I had just used 3 dates( 12/31/2021, 12/1/2021, 11/30/2021) for sample and my system setting is mm/dd/yyyy so I got 11/30/2021.

It’s not weird, it’s behaving the way it is intended to. It’s a text comparison you can see the number 01 is lowest from all the day of date so it will return 01, also if you see the sample I used here 11 is minimum so I got the answer as 11/30/2021.

If you don’t want to use PQ then can see if you can use change data type in Data pane, that will give you correct result. Change “Text” to "Date"

image

Thanks
Mukesh

5

Thanks Mukesh, decided to go down the Power Query route :slight_smile:

1 Like