Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Date Layout & Fiscal Calendar

I am using a fiscal calendar created in Excel.  (Available for review) Link

 

When I use my Month and Year category, my dates aligned properly.  See below.

 

 

However, when I use Quarter and Year, I cannot get the proper chronology.

 

 

 

When I try and use the "Sort By" function in the Modeling tab, I receive the following message.  

 

 

I am sure this problem starts with my custom Excel file - but I am really struggling with how to be use my custom fiscal calendar, have each of the columns recognized as dates, and have them appear on the axis in an appropriate way.

 

Thanks for the help in advance.

 

 

Need Help
Show and Tell
Tips and Tricks

4 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    9 years ago

    Hi Anonymous,

     

    You could add another column "Z-Quarter and Year Number" in Query Editor. Then the column "Z-Quarter and Year" can be sorted by the new column. Here is the step.

     

    =Text.Combine({Text.Middle([FiscalYearQuarter],3,4),Text.Middle([FiscalYearQuarter],1,1)})

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      v-jiascu-msft

       

      This seems like the perfect solution, however my limited experience with Query Editor (and the fact that your fieldnames don't coincide with mine) is preventing me from reproducing and doing what you have.

       

      Can you help me understand the operation in greater detail so I can translate it to my custom calendar, and extend it to other columns where this solution may also be appropriate.

       

      I am including the link where my fiscal calendar resides for your reference.  Much appreciated!

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        9 years ago

        Hi Anonymous,

         

        I found an easy way after looking deep into your table. Here it is:

        1. Add a new column with this formula.

        YearQuarterNumber =
CONCATENATE ( [Z-Year], [Z-Quarter] )

        2. Set "set by column" of "Z-Quarter and Year" to  "YearQuarterNumber".

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

        About the solution adding a column in the Query Editor, the details are:

        1. Open Query Editor;

        2. "Add column" -> "Add custom column";

        3. Rename the column;

        4. Paste the M query code;

        Text.Combine({Text.Middle([FiscalYearQuarter],3,4),Text.Middle([FiscalYearQuarter],1,1)})

        Text.Combine and Text.Middle are two funcions of M query. Please refer to Text.Combine. and Text.Middle

        5. Click "OK".

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

        Best Regards!

        Dale

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    9 years ago

    Hi Anonymous,

     

    Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

     

    Best Regards!
    Dale