How to convert text 06/ to date in power bi Table View (text to date conversion)

[KarenQ]

Hi, I am working on a sales data table in Table View, having 09 Data Period and 2023 as Data Period on two columns to represent current month Mar 2024 as an example.

I created  the below:

MonthNo = if('Sales'[Data Period]<7, 'Sales'[Data Period]+6, 'Sales'[Data Period]-6)

Year = if('Sales'[Data Period]>6,'Sales'[Data Year]+1,'Sales'[Data Year])

Full = 'Sales'[MonthNo]&"-"&'Sales'[Year],

then comes the issue when I created the below. 

FullMonth = datevalue('Sales'[Full])

Strangely this way of doing dates have been working to me all the time in the past and it even works on my another Bi file. 

not sure why but it gives below. 

it is perfecting working as per below in my other Bi files.

Anyone could look at this for me please. 

[lbendlin]

How often does this data change?  Right, never!  There is no need to compute that.  Use an external reference table.

[KarenQ]

hi thanks for prompt reply. This sales data is udpated daily so I do need to build it into modeling. 

[lbendlin · Accepted solution]

Use a calculated column to convert the string into proper ISO8601 format

yyyy-mm-dd

You can use DATE(year, month, day)  to achieve the same (with day replaced by 1)

[KarenQ]

thanks a lot.