Create YYYY-MM column

[tylerdv]

Hi Guys

I am trying to create my monthly reporting where i can show the results for the last 12 months (eg April 16 to April 17

I am having issues sorting the data in chronological order.

Using the 'Add Month #' and 'add year' transforms i have managed to get them in, but when merging then using the sort column function. September 2016 (20169) is placed after Octomber 2016 (201610) due to Power BI reading 20161 as lower than 20169)

How can i create a column that will place a 0 in the YYYYMM format so i can have 201609 which should read correctly in Power BI

Thanks Heaps

[Sean]

@tylerdv

The easiest way would be to reference your Date Column like this

YYYY-MM Column = VALUE ( FORMAT ( 'Calendar'[Date], "YYYYMM" ) )

[tylerdv]

Hi @Sean

Thanks for the response, when doing that the values come out as decimal numbers 

42614

62644

62675 etc

When formating them to date it includes the dd-mm-yyyy again

[KHorseman]

@tylerdvno he means you should create a new column using that formula. Then you can use that column as a sort value for your month column using the Sort By Other Column button.

[tylerdv]

Is there a link shwoing me how to do this properly? 

Can work it out sorry.

[Sean · Accepted solution]

@tylerdv

My suggestion above was to create a new DAX Column referencing your Date column

That would be on the Modeling Tab - New Column (NOT in the Query Editor)

YYYY-MM Column = VALUE ( FORMAT ( CalendarTable[Date], "YYYYMM" ) )

If you want this done with M in the Query Editor - Add Column tab - Custom Column

and use the columns Month and Year (which I assume you added using the From Date & Time option on the Add Column tab)

= Number.ToText([Year]) & (if [Month] < 10 then "0" else "") & Number.ToText([Month])

or if you want to refence the Date column again

= Number.ToText(Date.Year([Date])) & (if(Date.Month([Date])) < 10 then "0" else "") & Number.ToText(Date.Month([Date]))

there may be an easier way with M (like there is in DAX using FORMAT and VALUE with no IF statement)

Perhaps @MarcelBeug can tell us?

However that should do it!

If not post a screenshot of what goes wrong and where?

[Anonymous]

I know this is an old Post but hopefully someone might find this useful. 

Using M in Power Query you can create the format YYYYMM by using the following

Table.AddColumn(#"Renamed Columns1", "Period Key", each Date.ToText([FullDate],"yyyyMM"))

This will add a 0 in front of the months 1-9 automatically. [Full Date] is just a date column

[tylerdv]

@Sean

Query Editor custom column works a treat!

Thanks heaps

[MarcelBeug]

@Sean, @tylerdv

My suggestion would be

= Text.From(100*[Year]+[Month])

or

= Text.From(100*Date.Year([Date])+Date.Month([Date]))

Alternatively you can omit the Text.From.