Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
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?
The easiest way would be to reference your Date Column like this
YYYY-MM Column = VALUE ( FORMAT ( 'Calendar'[Date], "YYYYMM" ) )
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
@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.
Proud to be a Super User!
Is there a link shwoing me how to do this properly?
Can work it out sorry.
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?
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