Forum Discussion
Create YYYY-MM column
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
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! :smileyhappy:
If not post a screenshot of what goes wrong and where?
8 Replies
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.
