topic Re: Date table sorting error in DAX Commands and Tips

Date table sorting error

Mous007 — Mon, 16 Mar 2020 17:06:20 GMT

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

Re: Date table sorting error

Greg_Deckler — Mon, 16 Mar 2020 17:34:03 GMT

Perhaps Sequential?

https://community.powerbi.com/t5/Quick-Measures-Gallery/Sequential/m-p/380231#M116

Re: Date table sorting error

Mous007 — Mon, 16 Mar 2020 17:58:04 GMT

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.