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.

efglynn's avatar
efglynn
Icon for Advocate IV rankAdvocate IV
9 years ago

I wanted to compare months for several years side-by-side in plots, so I created a new variable MMYYYY for sorting purposes:

 

MMYYYY = FORMAT('Calendar'[calendar_nk],"MM-yyyy")

 

PowerBI tells me this computed field is a text type, as expected.

 

After I sort this new column, the dates are not in the text order I'm expecting.

 

I see:

 

First entry:

07-2015

 

Last entry

06-2014

 

The order of some middle entries doesn't make any sense to me, e.g.,  ... 12-2016, 04-2017, 06-2017, 01-2017 ...

 

I'm expecting the order to be something like 01-2014, 01-2015, 01-2016 ....  12-2014, 12-2015, 12-2016

 

How do I order data for a visualization in MM-YYYY text order?

efglynn's avatar
efglynn
Icon for Advocate IV rankAdvocate IV
9 years ago

I tried a numbers only approach and I still couldn't get "sort by column" to work:

 

MMYYYY = 10000*'Calendar'[month_num] + 'Calendar'[year_num]

 

Why doesn't "Sort by Columns" give some sort of diagnostic message when it refuses to sort a column?

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

    What happens when you follow the instructions as given in my last post? Did you try that approach? The formula works, the sorting works. The screenshot shows it.

     

     

    • efglynn's avatar
      efglynn
      Icon for Advocate IV rankAdvocate IV
      9 years ago

      Thank you for your reply.  I tried to create what you suggested:

      DateSort =Date.ToText(‘Calendar’[full_date],"yyyyMM")

       

      But I see:  The syntax for '.' is incorrect.  (DAX(Date.ToText('Calendar'[calendar_nk,"yyyyMM"))).

       

      I'm not sure why the "M" formula does not work here, but this does what I think you suggested:
      DateSort = FORMAT('Calendar'[calendar_nk],"yyyyMM")

       

      But I still cannot "Sort by Column" after creating the new DateSort column.  As far as I can tell, Sort by Column silently does nothing.

       

      Perhaps my problem is my data model is not a single table (query).  I'm working with 17 tables that are inter-connected.  For some reason, I cannot sort my Calendar table to create the graphic order I want.  The main table with the data of interest has 7 relationships with other tables in addition to the Calendar table.

       

      Is it possible PowerBI wants the whole data model re-ordered to create a graphic, and PowerBI for some reason won’t sort my Calendar table?

       

      I am new to PowerBi, but know R much better. In R, one can extract data and re-order it if necessary to create a graphic without changing the order in the original data source.  Is PowerBI not as flexible?  In PowerBI is it possible to change the order for a visualization without changing the order of the original data?