locked
[Power Pivot] Date format MMM-YY setting RRS feed

  • Question

  • Dear All,

    I want to set a date format is Dec-17.

    I use the DAX format([date],"MMM-YY) in power pivot, the result is work but it is not a data format (the result is text format).

    and when I change it to date format, the result like Dec-17 will be changed to other date format, and the option of it is base on the computer location / language setting.....

    How can I add the format MMM-YY to become a date format setting? Thank you!!

    David 

    Wednesday, December 27, 2017 11:00 AM

Answers

  • Hi David,

    Thanks for your question.

    I agree with Darren, you can create a calculated column as (Year(<column>) * 100) + Month( <column>), this column will store as  numeric value(something like 201712 for Dec-17), then you can sort your column (DAX format([date],"MMM-YY) by this new calculated column (Year(<column>) * 100) + Month( <column>). See below similar blog:
    https://www.excelguru.ca/content.php?252-Sorting-A-Column-Of-PowerPivot-Data-By-Another-Column


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, December 28, 2017 4:36 AM

All replies

  • What you're asking is not possible

    As if base datatype has to be a date it has to have day part information also

    The moment you get it onto any other format, it gets converted to character based data

    If you attempt is for sorting the values still based on the date sequence even after applying the format you can do this

    For the formatted column select click SortBy column option from above

    In the pop up screen set sort column as your original date column.

    You can see this as an example

    https://blogs.technet.microsoft.com/jessmeats/2012/06/12/powerpivot-sort-by-column/


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, December 27, 2017 11:17 AM
  • How can I add the format MMM-YY to become a date format Setting?

    Hello David,

    But that's not a date, it's a month-year combination and that's of type string; not date.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, December 27, 2017 11:23 AM
  • So usually the main issue with having a string in the MMM-YY format is that it does not sort in month order. To fix this you can create a numeric column with the following expression (Year(<column>) * 100) + Month( <column>) . This will create a number in the form of YYYYMM and you can use that as the SortBy column for your MMM-YY string. 

    http://darren.gosbell.com - please mark correct answers

    Wednesday, December 27, 2017 10:31 PM
  • Hi David,

    Thanks for your question.

    I agree with Darren, you can create a calculated column as (Year(<column>) * 100) + Month( <column>), this column will store as  numeric value(something like 201712 for Dec-17), then you can sort your column (DAX format([date],"MMM-YY) by this new calculated column (Year(<column>) * 100) + Month( <column>). See below similar blog:
    https://www.excelguru.ca/content.php?252-Sorting-A-Column-Of-PowerPivot-Data-By-Another-Column


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, December 28, 2017 4:36 AM