Forum Discussion

VPonnarath's avatar
VPonnarath
Frequent Visitor
7 years ago
Solved

Sorting week is not working

Hello,

 

I have created a calculated column for the "Week Name-Month" in my date table which display the Month and the week number of the particular month. 

 

Week Name-Month = CONCATENATE('Date'[Fiscal Month],CONCATENATE("- Week",'Date'[Week Number of Month]))

 

I am trying to use Week Name-Month column in my Line chart Axis and it is working, but we have a problem in sorting . Its not getting sorted in the order of the month, it sorts in a wired way. I tried t use the "Sort By Column" Functionality and tried sorting against Week number of the month. But, that dosen't work . 

 

Can anyone please help me with this? 

 

Thanks.

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    VPonnarath,

    Please update your formula to the following, then change data type of week sort column to whole number.

    Week Sort Column = CONCATENATE('Date'[Calendar Month Number],'Date'[Week Number of Month])



    This way, when you sort Week Name-Month column by Week Sort Column, you will get expected sort.



    Regards,
    Lydia

     

9 Replies

  • parry2k's avatar
    parry2k
    Super User
    7 years ago

    VPonnarath you are on right track to using "sort by"

     

    What is your fiscal month? Is it "Month name" or "Month Number"? Can you share screen shot how your week and sort column looks like?

    • VPonnarath's avatar
      VPonnarath
      Frequent Visitor
      7 years ago

      Thank you for your response ! 

       

      See the below screen shot. I am trying to sor the "Week Name -Month " Cloumn with "Week Number of Month" Column. 

       

      In my Line chart when I use Week Name -Month on Axis, It is not dispaying in an order

       

      Line Chart- See the week order displaying

      • parry2k's avatar
        parry2k
        Super User
        7 years ago

        i think your sort column should be

         

        MM-Week Number of Month, like 

         

        01-01

        01-02

        01-03

        ...

        12-01

        12-02

        12-03

         

        And use this new field to sort your "Week Name-Month" field

         

    • peter123's avatar
      peter123
      New Member
      6 years ago

      Hello,

       

      Looking for some help and it seems you have some good experience with this.   I have a Line Chart that shows revenue for current year and prior year with the axis as Month Name and Week Ending date (ie Apr-04, Apr-11, etc.).   I cannot get it to sort properly and I've tried to sort that table column my many other columns.  I get the error message below and I've tried to sort by columns with various data structures that should satify the error.   Please help! lol 

       

      "We can't sort the 'WeekEnding' column by 'YearWeekSort'. There cant be more than one value in...."