Forum Discussion

Sean's avatar
Sean
Community 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.

LanceDelano's avatar
LanceDelano
Microsoft Employee
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.

  • apamau's avatar
    apamau
    New Member
    11 years ago

    Yes, pls make it easier to auto detect or let us mark and easily inform the system this is month, year, a date or what ?

    thanks and hope that's coming in fast.

      • Anonymous's avatar
        Anonymous
        Not applicable
        10 years ago

        I am too having this issue, surly it is a basic problem?

    • dexterz's avatar
      dexterz
      Helper II
      9 years ago

      Date =ADDCOLUMNS (

          CALENDAR ( "2010-01-01", "2014-12-31" ),

          "Year", FORMAT ( [Date], "YYYY" ),

          "Quarter", "Q" & FORMAT ( [Date], "Q" ),

          "Month", FORMAT ( [Date], "MM" ),

          "Month", FORMAT ( [Date], "MMM" ),

          "Weekday", FORMAT ( [Date], "ddd" ),

          "WeekdayNum", WEEKDAY ( [Date], 2 ),

          "Day", FORMAT ( [Date], "dd" ),

          "Cal-Month", FORMAT ( [Date], "YYYY-MM" ),

          "Cal-Month", FORMAT ( [Date], "YYYY" ) & "-Q"

              & FORMAT ( [Date], "Q" ))

      • teylyn's avatar
        teylyn
        Advocate III
        9 years ago

        amysmith, you are not attempting to answer the question. The link just leads to a generic landing page in your site. I assume you posted that link to attract traffic. I have reported your post as spam.

  • Sean's avatar
    Sean
    Community Champion
    11 years ago

    Thanks! That would be great and would save us time when dealing with all date columns in a data model. Looking forward to it!

  • starmoonknight's avatar
    starmoonknight
    Helper II
    10 years ago

    Hi Lance,

     

    I just found that if we create a new column that uses FORMAT to customise a Date type column, it is no longer of data type Date but Text.

     

    The thing is that the provided formats for Date could not satisfy all needs, and if we use FORMAT, in the Report View, it'll be sorted alphabetically. It's more than better that we can customise the format but PBI still knows that it is a Date not a Text, so we can make use of the hierarchy and sort it in a meaningful way.

  • mkorby's avatar
    mkorby
    Regular Visitor
    10 years ago

    Great. Looking forward to getting this useful Power BI Online feature in Power BI Desktop.

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Any news on when this will be rolled out?

    • EZgrafton's avatar
      EZgrafton
      Advocate II
      9 years ago

      I don't know if you found a solution for this, but if you didn't just follow these simple steps:

      1. create a table with the months / year
      2. assign a numerical order to them (1, 2, 3, etc.)
      3. link the month / year field on the newly created table to the month / year field on your original table
      4. sort the month / year field on your original table by the numerical order you created on 2

      It should work. :)

  • Haider121's avatar
    Haider121
    Frequent Visitor
    9 years ago

    ITs now 2017 where is this solution? still cannot sort by chronolological test name month?

    • teylyn's avatar
      teylyn
      Advocate III
      9 years ago

      You sound upset. It is possible to sort visualisations and tables properly, even if only the month name is being used.

       

      Start in the query and add a custom column called DateSort with this M formula:

       

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

       

      Replace [Date] with your desired date field. The new column will show the values like in this example. Note how the order is not chronological in the query.

       

       

       

       

       

       

       

       

       

       

       

       

      Close and apply the query and go to to data tab and click the Modelling ribbon. Select the Month Name column, click the "Sort by Column" dropdown. Select the DateSort column you just created.

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

      Now you can create data visualisations, charts or tables, where you use just the month name. If you sort by that month name, the result will be chronological, not alphabetical or only by month number. Not in the screenshot how February is at the bottom because it is a date in 2017, whereas all other dates are in 2016. For your benefit, another table shows the full date.

       

      If that is not what you are trying to achieve, maybe you could politely explain your details.

       

       

       

       

       

       

       

       

       

       

       

      • Haider121's avatar
        Haider121
        Frequent Visitor
        9 years ago

        No I was frustrated earlier but thanks. Your solution is ok for data within powerBI but we run a SSAS tabular model from whichpowerBI reads the data from. In which case the sort by column drop down is greyed out and powerBI has no control of the data.

         

        But its fine I found the option within SSAS properties of the data column where I can change the 'sort by column' and it works. Its just that this whole experience was not intuitive like any of the other BI platforms like qlikview or crystal. You have to look for the  needle in a bunch of needles with powerBI.