cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NLeelakrishna
Advocate I
Advocate I

How to change format for date field with DAX expression while get data as direct query in power BI

Hi,

 

I want change format for date with DAX expression while direct query in Power BI. I have tried with format function in DAX but getting error like "Function 'FORMAT' is not allowed as part of calculated column DAX expressions on DirectQuery models." 

 

Thank you.

6 REPLIES 6
Jessica_Seiya
Solution Sage
Solution Sage

Try change the regional settings of Power BI, and then re-format your date. This allows you to change the format from dd/mm/yyyy to mm/dd/yyyy. Remember this is global setting, all your date will be changed based on your selection

2018-03-22_16-29-34.png

 

2018-03-22_16-30-14.png

 

 

 

Hi Jessica,

 

Thanks for your reply. We have tried as you given information , we got many formats. But we need "dd-MMM-yyyy hh:mm:ss"

(22-Mar-2018 02:07:43) format. that is not available in that formats. This format we can achive by using DAX only or any other options is there? If any options avaliable pls let us know.

 

Thank you.

JoHo_BI
Resolver V
Resolver V

Hi @NLeelakrishna,

 

You can try just extracting each individual part and casting it to a date. So if you want a MM/DD/YYYY format, try:

 

= MONTH(Date[date])&"/"&DAY(Date[date])&"/"&YEAR(Date[date])

 

Hope that helps!

Hi Joho,

 

Thank you for the idea, we will try and let you know.

 

Without DAX is that possible in the data modeling, Like custome format like (dd/MMM/yyyy).

As far as I know it get taken from your local DateTime settings and there's no way to override it without DAX

Thank you Joho for your confirmation.

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors