Format Date in Direct Query mode

[Anonymous]

Hi,

I have a date column in my metadata in simple date format.

How can I format it to be shown as below. I want it to be shown as Month'Year format in sorted manner.

Please Note: I'm using direct query mode so cant use format option.

Regards,

Himanshu

[Anonymous]

Anyone has a idea how to format a simple date to"YYYYMM"

format in direct query?

This will resolve my issue.

[Samarth_18]

Hi @Anonymous 

You can add below format in custom formatting to get your desired output.

Thanks

[Anonymous]

I'm getting this output

How to get month wise break up like below:

[Samarth_18]

You can create two calculated column one for format your date and another column for sorting of your formatted date column. Below are the required code for both format and sort date column.

formatted_date = FORMAT(date_table[Date],"MMM YYYY")

Sort = FORMAT(date_table[Date], "YYYYMM")

[Anonymous]

@Samarth_18  Yes this is easy and can be done when in import mode but format doesnt work in Direct query mode hence facing this issue. 

I have created the Month'Year using concat but sorting is the issue. Sorting is not happening correctly for me.

[selimovd]

Hey @Anonymous ,

why not? Custom format works also in DirectQuery mode, I just tried it:

And the storage mode is DQ:

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

I want to present the data in the below format, can you please help me with the steps how to use it as its not working for me at the moment.

Its showing me very incorrect data.

It should be something like below , i have created some calculated column n done concat but sorting is not working properly.