CODEWITHSUNDEEP
sqlssasdata-cube
Cade
Cade

Reputation: 38

How to get dates inside of SSAS cubes to display in a different format

The dates in SSAS data cube are processing like 201801-201802-201803 and so on. for some reason I can't figure out how to get them to display like June-2018. I know there's got to be a way to code dates in dimensions that will display them like that, I just can't find it or anyone who knows.

For year of order I use 

CONVERT(char(4), YEAR(OrderDate))

For Month of order I use 

CONVERT(char(4), YEAR(OrderDate)) +  RIGHT('0' + CONVERT(varchar(2), MONTH(OrderDate)), 2)

These are all based off of my OrderDate column that I'm bringing in that outputs into my database like 

"2018-08-20 16:48:10.253"

I have surfed a lot but not able to get suggestion. Please suggest me on this.

Upvotes: 1

Views: 277

Answers (1)

userfl89
userfl89

Reputation: 4790

If you're using a Tabular Model, within SSDT go to the Grid view and select the date column that you want to change the format of. In the Properties window, make sure the column already has a data type of Date and set the Data Format to MMMM yyyy. This won't have the -, but you can add a calculated column with the DAX formula below that will include this, just be aware of the additional overhead that comes with calculated columns.

FORMAT(OrderDate[Date], "MMMM-yyyy")

Upvotes: 1

Related Questions