Forum Discussion
DAX Query Format Date
Hi all,
Using PBID Sept, 2025.
Is it possible to return just a formatted date without first referencing the date column
For example, I'm doing this
EVALUATE
SUMMARIZECOLUMNS(
Bookings[Date],
"xDate",FORMAT( SELECTEDVALUE( Bookings[Date] ), "MM/DD/YYYY" )
)
Wed, 01 Jan 2025 00:00:00I tried removing the first line - no luck
Feels like I am violating DRY.
Any ideas?
Thanks,
You can use the following DAX query to return only a formatted date, avoiding the ugly datetime string when copying to Excel:
EVALUATE
SELECTCOLUMNS(
Bookings,
"xDate", FORMAT(Bookings[Date], "MM/DD/YYYY")
)- This will output only the xDate column with dates formatted as MM/DD/YYYY.
- Excel will see the result as plain text, so the original datetime format like Wed, 01 Jan 2025 00:00:00 is avoided.
- You don’t need to reference the original Bookings[Date] column separately, which keeps your solution clean and avoids violating DRY principles.
If this answer helped, please click Kudos or Accept as Solution.Best regards,
Vaibhav Mahajan
15 Replies
You can use the following DAX query to return only a formatted date, avoiding the ugly datetime string when copying to Excel:
EVALUATE
SELECTCOLUMNS(
Bookings,
"xDate", FORMAT(Bookings[Date], "MM/DD/YYYY")
)- This will output only the xDate column with dates formatted as MM/DD/YYYY.
- Excel will see the result as plain text, so the original datetime format like Wed, 01 Jan 2025 00:00:00 is avoided.
- You don’t need to reference the original Bookings[Date] column separately, which keeps your solution clean and avoids violating DRY principles.
If this answer helped, please click Kudos or Accept as Solution.Best regards,
Vaibhav Mahajan
Hi tecumseh ,
it looks a bit unusual how you use SUMMARIZECOLUMNS because you group by Bookings[Date] and then repeat Bookings[Date] as the calculated column for every group.
Depending on your needs, the following two options could help you:
1) SELECTEDVALUE doesn't really care about the group by. If you really need a similar expression to the one you mentioned you can try to replace SELECTEDVALUE by VALUES
2) If 1) is not a solution for you, it would be helpful to get a better understanding of what exactly you wanna do. Do you need for example only the distinct values of Booking[Date]?Thanks Hans-Georg_Puls
There are other columns as well I removed to separate the wheat from the chaff.
At then end of the day, I want a date in US Format: "MM/DD/YYYY"
Prefer no other date columns.
Thanks,w
Hi tecumseh ,
some more details would be helpful. Here are a few options and questions:
1) Does the data look fine in Power BI? But as soon as you paste them into excel you get the horrible string (Wed, 01 Jan 2025 00:00:00). That would sound like a wrong cell format in Excel.
2) Where in Power BI are you evaluating the SUMMARIZECOLUMNS expression? What is the result for the first two columns? Could you provide an extract from the table?3) What is your goal? Generating a table in Power BI to export it to Excel by Copy & Paste?
Hi tecumseh,
AFAIK, although format function will convert your date column's datatype from date to text, it should work and give you dates in "MM/DD/YYYY" format when you are copying a cell from table or exporting the table.I tried to double check and below is the result of export (same is the result on copying table values and pasting to excel/csv):
If you don't use the format function and try to change format of date column (with date datatype) from the column tools -> Format section, it will change for the PBI screen but not on exports. This is because excels take system's regional format and Power BI considers regional settings set for the pbix.
Give a Thumbs Up if this post helped you in any way and Mark This Post as Solution if it solved your query !!!
Proud To Be a Super User !!!
LinkedInHi,
If you are open to the idea of using Power Query, then do the cleaning up there instead.
EVALUATE
SUMMARIZECOLUMNS(
"xDate", FORMAT(Bookings[Date], "MM/DD/YYYY")
)No date column reference needed. Single FORMAT handles both grouping + display.
If this answer helped, please click Kudos or Accept as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pandeHi tecumseh , hi Kedar_Pande ,
Mmmmh, I can't reproduce that. The expression gives me an error:
A single value for column 'Date' in table 'Table' cannot be determined. This can happen when a measure or function formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.The following expressions might be helpful:
SELECTCOLUMNS(DISTINCT('Table'[Date]),"xDate", FORMAT('Table'[Date], "MM/DD/YYYY"),"any Measure", [Your Measure])
It gives you the distinct values of Date as a formatted date string and the ability to add additional columns grouped by the dates.Hope that helps.Thanks Kedar_Pande
Does not work for me.Hi tecumseh,
I tried to reproduce your scenario using a small sample Bookings table, and I was able to get the expected US-formatted date output by using the following DAX query:
EVALUATE ADDCOLUMNS( VALUES(Bookings[Date]), "xDate", FORMAT(Bookings[Date], "MM/DD/YYYY") )This approach ensures that each date is formatted correctly as MM/DD/YYYY without returning the long datetime string you were seeing earlier. I’m attaching the sample .pbix file I used, in case you’d like to compare it with your model or test the behavior on your side.
Best regards,
Ganesh Singamshetty.
Hi tecumseh,
Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to Hans-Georg_Puls, Kedar_Pande, Ashish_Mathur & Anand24 for sharing valuable insights.
Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.
Thank you for being part of the Microsoft Fabric Community.
Hello tecumseh,
Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.
Thank you for being part of the Microsoft Fabric Community.
You can use the following DAX query to return only a formatted date, avoiding the ugly datetime string when copying to Excel:
EVALUATE
SELECTCOLUMNS(
Bookings,
"xDate", FORMAT(Bookings[Date], "MM/DD/YYYY")
)- This will output only the xDate column with dates formatted as MM/DD/YYYY.
- Excel will see the result as plain text, so the original datetime format like Wed, 01 Jan 2025 00:00:00 is avoided.
- You don’t need to reference the original Bookings[Date] column separately, which keeps your solution clean and avoids violating DRY principles.
If this answer helped, please click Kudos or Accept as Solution.Best regards,
Vaibhav Mahajan
