DAX Query Format Date

[tecumseh]

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" )
)

That first line returns a horrible string when I copy the table and paste to Excel:

Wed, 01 Jan 2025 00:00:00

I tried formatting the first line - no luck
I tried removing the first line - no luck
Feels like I am violating DRY.
Any ideas?
Thanks,

w

[v-ssriganesh]

Hi @tecumseh,

Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to @DNMAF, @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.

[Kedar_Pande]

@tecumseh 

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

[DNMAF]

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.

[Ashish_Mathur]

Hi,

If you are open to the idea of using Power Query, then do the cleaning up there instead.

[Anand24]

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 !!! LinkedIn

[DNMAF]

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?

[DNMAF]

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]?

[tecumseh]

Thanks @DNMAF 

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