show date as yyyy-mm and sorted by month

[Lunge_PTP]

I have created a new column with this: 

Indbetaling_År_MD = YEAR([Indbetalingsdato]) & "-" & MONTH([Indbetalingsdato])

when data is presented in a table I get this:

2017-1

2017-10

2017-11

2017-12

2017-2

I would like it, to be sorted/shown this way: 2017-01, 2017-02, ..... 2017-11, 2017-12

When I run the data through Excel and do the same task, and then import the data as Excel I get the date as: 2017 january, 2017 february which is fine with me.

 How do I solve this problem?

[Payeras_BI · Accepted solution]

Hi @Lunge_PTP ,
You can also use FORMAT for this purpose:

FORMAT('Calendar'[Indbetalingsdato],"YYYY-MM")

[Anonymous]

Generally I create the Calendar using Power Query. Here you can add a column like this:

Column Name: "YearMonthNo"

Expression

= Text.Combine(
{Date.ToText([Indbetalingsdato], "yyyy"), "-", Date.ToText([Indbetalingsdato], "MM")}))

[Mohammad_Refaei]

Go to data view, select your new column, in column tools choose sort by (Date).

[Samarth_18]

Hi @Lunge_PTP 

Instead you can duplicate your date column and change its date format directly from ribbon as shown in below screenshot. Since the way you following to create a column actually its converting that column as text and its causing issue while sorting.