Can not modify power bi measure to Date Format

[razamatazzz]

Hello All, I have created a power BI report which is embedded into D365. I created a measure which would format the date based on the location the user is i.e DD/MM/YYYY or MM/DD/YYYY.  I need to do this in DAX, because i need to get the location information from the APP (D365 FO) as this is an embedded report. (can not get this from the power bi workspace) 

All is working fine, however the measure is coming back as a text and not allowing me to format it to Date, which does not allow to sort it properly especially when the date format is DD/MM/YYYY. 

I have tried changing the measure twice but still no luck . I have tried using Format function and date function (please see below)

val1 is the value coming back from FO 

val2 is the date that i am trying to format. 

Any help would be DEEPLY appreciatted 

[tamerj1]

Hi @razamatazzz 
FORMAT functions return string data type. For sorting you need to use a sorting measure "the SELECTEDVALUE ( Table[Date] "  will be jsut fine, and the hide it.

[razamatazzz]

Thanks Tamerj but i need to either use format or the date function to change the format of the Date to dd/mm/yyyy incase the location is based out of the US. Are you saying i can combine the format and selectedvalue formula ?

[tamerj1]

@razamatazzz 

I'm saying you have to have two measure. One eith FORMAT to disply and one with SELECTEDVALUE to sort. 

[razamatazzz]

I dont think that will work as i have three of these measures and i want the user to be able to sort using the table headers dynamically if they want to 

[v-chenwuz-msft]

Hi @razamatazzz ,

It seems impossible to do that. If possible, you can use format([date]," en-US mm-dd-yyyy") and use sort it by column to sort it. Because you can not identify 01/04/2022, it is January or April.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.