Forum Discussion

Sean's avatar
Sean
Icon for Community Champion rankCommunity Champion
11 years ago
Solved

Month Columns in PBI Desktop

I noticed that when I load my data directly into PBI Online I automatically get YEAR and MONTH columns added next to all DATE columns I have! What is even better is that this MONTH column that PBI On...
  • LanceDelano's avatar
    LanceDelano
    11 years ago

    We're currently working on this for the desktop .... and it will be a unified experience in both the desktop and service.  There are several date time features that will show up.  This kind of automatic behavior is among the first that will arrive.

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
11 years ago

Create a new column called "MonthSort" or something and use the formula:

 

=FORMAT([Date],"mm")

 

This will give you a two digit number for month.

 

Select your Month column, Modeling tab and choose "Sort by Column" and set it to MonthSort column.

teylyn's avatar
teylyn
Icon for Advocate III rankAdvocate III
9 years ago

Why is that not working for me? My column "CreatedDate" is a date column. But when I use the formula

 

=FORMAT([CreatedDate],"yyyymm")

 

I get an error "the name 'FORMAT' wasn't recognized. Make sure it's spelled correctly.

 

 

 

  • teylyn's avatar
    teylyn
    Icon for Advocate III rankAdvocate III
    9 years ago

    OK, found it. The FORMAT function is a DAX function, not an M function. It would be a lot easier if people would point that out when posting a formula.

     

    If you want to create the column in M, then use

     

    =Date.ToText([CreatedDate],"yyyyMM")

     

    With lower case yyyy and uppercase MM.