Reordering Month and Year in formatted Fiscal Period column

0
votes

I am trying to reorder a fiscal period column from alphabetical order to chronological order. That is, I have this column, which displays values as month-year (showing all distinct values in Query Editor): Period_Name Column

I would like to order this column chronologically, like this: JAN-18, FEB-18, MAR-18, APR-18, etc. in order to use it to plot a time series.

Right now if I try to change the column's data type in Query Editor to Date, it thinks that year is actually the day, and then assumes the year to be the current year (namely 2019); that is, APR-18 becomes April 18, 2019 instead of what it should be, April 2018, with no day (this is a fiscal period column). The custom date and time formats with the FORMAT function (https://docs.microsoft.com/en-us/dax/custom-date-and-time-formats-for-the-format-function) do not appear to recognize the current format that this column is in as month-year, and just spit out the same values back when I try to use it in a new calculated column.

Any help would be much appreciated!

2
powerbidax
This is interesting, I am based in Europe, I made a test by creating your exampe in excel, import it. Power-Bi made itself the correct date (what you where looking for). If creates the dates like 1-4-2017, 1-4-2018, 1-8-2018, etc – Aldert
are you using a calendar table? Or is this field in your fact table? – StelioK

2 Answers

0
votes

Being that your field is TEXT it will be sorted alphabetically. You can create a 'Sort By Column' to sort your data by doing something like this: 

Sort by Column := 
VAR YearNumber =
    RIGHT ( 'Table'[Column], 2 ) + 2000
VAR CurrentMonthName =
    LEFT ( 'Table'[Column], 3 )
RETURN YearNumber &
    SWITCH (
    CurrentMonthName,
    "JAN", "01",
    "FEB", "02",
    "MAR", "03",
    "APR", "04",
    "MAY", "05",
    "JUN", "06",
    "JUL", "07",
    "AUG", "08",
    "SEP", "09",
    "OCT", "10",
    "NOV", "11",
    "DEC", "12"
)

This will give you an index from which to sort your data by. Create this as a calculated column convert type from text to whole number and then sort your original column by this new column and you should be golden.

2
votes

You should not change the datatype of Month-Year, instead sort the month-year column by another column, that could be of another datatype: Go into the table/data view, click in the column to be sorted, click "sort by column" and then select the surrogate sort column

You should also start working with a related Calendar table to manage these type of sortings correctly.

For example in your calendar table you have two columns. Month-year that contains data like "Feb-2019" like you want to use, then another column called YearMonth that contain values like 201902 and is of type Int.

Then simply sort the Month-Year column based on the YearMonth column and it will be sorted correctly.