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 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?