Forum Discussion

Mous007's avatar
Mous007
Helper IV
6 years ago
Solved

Date table sorting error

Hi everyone,

 

The extract of my current dates table:

DatesTable =
VAR MinYear = YEAR ( MIN ( 'Power BI users extract'[Date] ) )
VAR MaxYear = YEAR ( MAX ( 'Power BI users extract'[Date] ) )
RETURN
ADDCOLUMNS (
FILTER (
CALENDARAUTO( ),
AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )),
"Calendar Year", YEAR ( [Date] ),
"Month Name", FORMAT ( [Date], "MMM" ),
"Month Number", MONTH ( [Date] ),
"Weekday", FORMAT ( [Date], "ddd" ),
"Weekday number", WEEKDAY( [Date] ),
"Period", FORMAT( [Date], "MMM-YYYY" ),
"Week number" , WEEKNUM([Date], 2),
"Week", "W" & FORMAT( [Date], "WW-MMM-YY" ),
"Quarter", "Q" & TRUNC ( ( MONTH ( [Date] ) - 1 ) / 3 ) + 1)
 
The table below is showing the report views count per week:
 

 

So i have managed to create a date table with the help of other users and everything was fine untill i tried to sort one of my tables based on the week value. i tried to sort by week both ascending descending and im not getting the correct sorting. The only waz i can get it to work is when i add the week number to my table and the issue with this approach is on the following screenshot. Power bi is putting the last weeks of 2019 after the first weeks of 2020.

 

I would appreciate if anyone has an idea on how to tackle this issue.

 

Thanks in advance

 

calendar
dates table
DAX
sorting

2 Replies

    • Mous007's avatar
      Mous007
      Helper IV
      6 years ago

      Hello there Greg_Deckler , thanks a lot for your prompt reply.

       

      I have tried to following column based on your link but it seems that its not working;

       

      Column =
      VAR MaxWeeks = SUMMARIZE(ALL('DatesTable'[Week number]),'DatesTable'[Week number],"MaxWeek",MAX('DatesTable'[Week number]))
      VAR MyYear = [Calendar Year]
      VAR MyStart = SUMX(FILTER(MaxWeeks,[Calendar Year]<MyYear),[MaxWeek])
      VAR firstYear = CALCULATE(FIRSTNONBLANK('DatesTable'[Calendar Year],1),ALL(DatesTable[Week number]))
      VAR myNum = IF(MyYear=firstYear,[Week number],MyStart+[Week number])
      RETURN myNum
       
      and my table is still returning the following;
       
      maybe i am not using your technique correctly or i have made an error in my dax.
       
      Any recommendations would be appreciated.