CODEWITHSUNDEEP
datepowerbidax
nilsinelabore
nilsinelabore

Reputation: 5135

Power BI: Order data by YearMonth in DAX

I would like to create a YearMonth column in Power BI and group data in this order in visual plot, say histogram.

I tried to create a duplicate of the datetime column MY_DATE(format: 05-10-2018 12:00:00 AM) and convert it to Year-Month format in the ColumnTools ribon:

enter image description here

Although in the column the format looks correct, eg., '2018-03', when I plot the graph after grouping by YearMonth it is repeating itself, ie., having multiple columns of 2018-07.

Then I tried creating YearMonth with:

YearMonthText = YEAR(Table[MY_DATE]) & "-" & MONTH(Table[MY_DATE])

This looks good in both table column and was able to aggregate data when grouped by YearMonth, however, the table is not in chronological order, eg.:

2020-10   2020-11   2020-12   2020-7   2020-8   2020-9   2021-1   2021-2   ...

How can I get the plot to show data in chronological order?

Upvotes: 0

Views: 472

Answers (1)

nilsinelabore
nilsinelabore

Reputation: 5135

Worked for me:

First, use:

YearMonthText = YEAR(Table[MY_DATE]) & "-" & MONTH(Table[MY_DATE])

to get YearMonth in text format(eg., 2018-3).

Then, convert Data type to Date type in ColumnTools ribon.

Upvotes: 0

Related Questions