convert 6 Jan 2021 to format DD/MM/YYY that is 6=06/01/2021 Get error msg

[admin11]

Hi All

I need to convert 6 Jan 2021 to  format DD/MM/YYY that is 6=06/01/2021

I have try below expression :-

DATE = DATE = DATE (

    RIGHT ( SALES_T[AR Invoice Date], 4 ),

    LEFT ( SALES_T[AR Invoice Date], 2 ),

    MID ( SALES_T[AR Invoice Date], 4, 2 )

)

I get below error msg :-

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

Hope some one can advise me where go wrong ?

Below link is my PBI sample file :-

https://www.dropbox.com/s/2ef4ms3gx8208k5/PBTI_V011.pbix?dl=0

Paul

[admin11]

Hi All

My date is :- 06 Jan 2021 & 09 Jan 2021 , It return as :- /2/1//2021

Due to my below expression is not correct :-

DATE = MID(SALES_I[AR Invoice Date],4,2) &"/"& LEFT(SALES_I[AR Invoice Date],2) & "/" & RIGHT(SALES_I[AR Invoice Date],4)

My Sample PBI file :-

https://www.dropbox.com/s/6cq0a9lfhyx8711/PBTI_V008.pbix?dl=0

Hope some one can share where go wrong ?

Paul Yeo

[Anonymous]

Duplicate of the post - community.powerbi.com/t5/Desktop/convert-6-Jan-2021-to-format-DD-MM-YYY-that-is-6-06-01-2021-Get/m-p/2255715#M820400 

[parry2k]

@admin11 not fully sure what the issue is? why not change the type of AR Invoice Date as date type in PQ?

[admin11]

@parry2k 

Thank you very much for look at my post.

I have create new post as below :-

https://community.powerbi.com/t5/Desktop/convert-6-Jan-2021-to-format-DD-MM-YYY-that-is-6-06-01-2021...

Paul

[parry2k · Accepted solution]

@admin11 if AR Invoice Date is already a date type, you don't need to create another one for the format, just use custom format Use custom format strings in Power BI Desktop - Power BI | Microsoft Docs and set it the way you want or add another column with the following but be careful, it will be treated as a text not a date 

New Date Column = FORMAT ( Table[AR Invoice Date], "dd/mm/yyyy" )

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[admin11]

@parry2k 

Thank you very much , it work 

Paul