Creating a Date

[JustinT]

Hi, I have a table which has the year and month as text in two separate columns. I need to create a single column which acts as a date so that I can use this as an axis on a bar chart,

I tried the expression 

Combined Date = [Year] & " " & [Month] 

which did create a new column and combine the text, but this doesn't order the information in the table, I assume because it isn't a date.

Any advice would be most welcome.

Thanks

[TomMartens · Accepted solution]

Hey,

I would do the following, by creating three new calculated columns.
The first column just concatenates the existing Year and Month columns (this will be used as axis), this concatenation can be done by this DAX statement:

Year-Month = 'Table1'[Year] & " " & 'Table1'[Month]

Than I create a real date column using this DAX statement:

Date = DATE('Table1'[Year],
    SWITch('Table1'[Month]
    ,"January", 1
    ,"February",2
    ,"March",3
    ,"April",4
    ,"May",5
    ,"June",6
    ,"July",7
    ,"August",8
    ,"September",9
    ,"October",10
    ,"November",11
    ,"December",12
    ),1)

Be aware that a date always needs a Day, for this reason I used 1 as the 1st of the month, the last parameter of the DAY(year,month,day) formula.

The 3 column is a running index that uniquely identiefies a the combination of year and month, i will use this column to order the "Year-Month" column. To create this index I use this DAX statement:

RunningMonthIndex = 
(YEAR('Table1'[Date])-YEAR(MIN('Table1'[Date])))*12+MONTH('Table1'[Date])

My final table would look like this:

Now in the Data view you can mark the Year-Month column, in the modeling menu choose "Sort by column" from the "Sort" ribbon and select RunningMonthIndex.

You can hide both columns "Date" and "RunningMonthIndex" in the report view, to minimize complexity for the users of the report.

Hopefully this is what you are looking for.

Regards

Tom

[JustinT]

Thanks - worked like a charm - just need to get my head around how it all comes together

[TomMartens]

Hey @JustinT,

great that it helps to solve your problem.

Basically: what you were looking for is something that could be used on the x-axis.

By default strings are sorted from A-Z, this makes sense for everything that does not contain a natural order, like the names of a product. But something that looks like a date has a natual order: January comes before April ...

For this reason, it is necessary to bring the strings that you want to use on the xaxis into its natural order. For this reason I created a Date column by using the existing Year and Month columns. The additional twist is that the DATE formula needs three number values to create a date. For this reason I used the SWITCH formula to translate the string July into 7, January into 1, ....

Just adding 1 as a constant for the Day part to the formula because three parts are needed.

This "translates" "2017 July" into the date 2017-07-01.

The above is just a preliminary for creating a sortindex for the column "Year-Month" without the need to "manually" create a table that contains the value 1 as the index for January, 2 for February ...

Basically the DAX statement builds a sequence starting with the first Year/Month combination. I would call it a simple sequence of 12.
That works this way ( (currentyear - firstyear) * 12 ) + currentmonth.

currentyear = YEAR([DATE]

firstyear = YEAR(MIN([DATE]))

Hope this helps to keep your head safe 😉

Cheers

Tom

[JustinT]

Wow - thanks Tom - I will give that a run !

[Stachu]

I think you can achieve the same by putting both Year and Month fields (2 separate fileds, not combined one) in the axis and drilling down with the 'fork' icon. You will still need to set custom order for the months from 1 to 12