SUMMARIZECOLUMNS with a formatting changed

[Mann]

Hi Guys,

I created a simple query to give me list of dates available in Date column in one of my AAS table. I used "Order By" to sort it and used this query result in Power BI paginated report as follows:

EVALUATE 
SUMMARIZECOLUMNS('Table1'[Date])
		ORDER BY 'Table1'[Date] DESC

I am getting results in "MM/DD/YYYY hh:mm tt"

I need to get results in "MM/DD/YYYY" format.

How can I change the above query to give me results in "MM/DD/YYYY" format with Summarizecolumns.

Thanks

[jenny1976]

Thank you so much for posting this. It solved my issue as well. It was driving me mad. 🙂 Have a great day!

[jdbuchanan71]

@Mann 

You should just be able to change the formatting on the column of your new table in the model and it will appear correctly wherever it gets used.

[Mann]

@jdbuchanan71 

I tried chnaging the formatting of this column to "MM/DD/YYYY" in AAS but when this query is used as a dataset in Power BI Paginated report it shows "MM/DD/YYYY hh:mm tt"

So I am thinking of changing the query in a way to give me "MM/DD/YYYY" only.

Thanks

[Mann · Accepted solution]

I achieved this in the query by this:

EVALUATE
VAR _TempTable= SUMMARIZECOLUMNS ( 'Table1'[Date] )
Return
SELECTCOLUMNS(_TempTable,"Date",FORMAT([Date], "DD MMM YYYY"))
order by [Date] desc