Forum Discussion
DAX Query Format Date
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
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-pande
Hi 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.v-ssriganesh
Yes, similar to my original post.
But my goal was to try to figure out how to get the formatted date without repeating Bookings[Date]. Feels like violating DRY (Don't Repeat Yourself)
Thanks,w
