Forum Discussion

S184019's avatar
S184019
Advocate III
7 years ago
Solved

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...
Data Modeling
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    7 years ago

    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 

S184019's avatar
S184019
Advocate III
7 years ago

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