Forum Discussion
Formatting not working properly
I've a simple table with a date field called "ServDate" formatted as a date *3/14/2001 with values ranging from 9/4/2018 to 3/29/2019. The table has ~134K rows. Creating a new column like this
MMM = MONTH(a[ServDate])
I get:
ServDate MMM
10/2/2018 10
10/2/2018 10
10/18/2018 10
... so this works.
But IF, we add this piece, MMM = FORMAT(MONTH(a[ServDate]),"MMM"), we get
ServDate MMM
10/2/2018 Jan
10/2/2018 Jan
10/18/2018 Jan
The month here is October, not January. Please let me know if you want the pbix.
Hi S184019,
Please modify the DAX like below:
MMM-YY = CONCATENATE(FORMAT(Dates[Date],"MMM"),CONCATENATE("-",RIGHT(YEAR(Dates[Date]),2)))Best Regards,
Qiuyun Yu
2 Replies
- v-qiuyu-msftCommunity Support
Hi S184019,
Please modify the DAX like below:
MMM-YY = CONCATENATE(FORMAT(Dates[Date],"MMM"),CONCATENATE("-",RIGHT(YEAR(Dates[Date]),2)))Best Regards,
Qiuyun Yu - S184019Advocate III
Even more simply, start from a blank PBIX. create a new table. Type this which will produce a 2 year date range of 731 rows:
Dates = CALENDAR(date(year(now())-2,month(now()),Day(now())),now())Then add a column to the table like this:MMM-YY = CONCATENATE(FORMAT(MONTH(Dates[Date]),"MMM"),CONCATENATE("-",RIGHT(YEAR(Dates[Date]),2)))One would expect to see values for the 3 letter month and 2 digit year, but instead, only Jan and Dec show up with their associated year. bi-support v-yulgu-msft