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 = 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-msft's avatar
    v-qiuyu-msft
    Community 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 

  • S184019's avatar
    S184019
    Advocate 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