Forum Discussion

Igotzepowerbi's avatar
Igotzepowerbi
Frequent Visitor
2 years ago
Solved

Week Number Column Out of Order

Hi, I am having an issue where my week numbers are out of order. Can someone help me with how to order them. I added the formula that is creating the table I am using. I have tried quite a few things with no luck so any help would be really appreciated. THanks so much!

 


DateAutoTemplate =
--  
--     Configuration
--  
VAR __FirstDayOfWeek = 0
----------------------------------------
VAR __WeekDayCalculationType = IF ( __FirstDayOfWeek = 0, 7, __FirstDayOfWeek ) + 10
VAR __Calendar =
    VAR __FirstYear = YEAR ( MINX ( { MIN ( 'POS Aggregate'[Week End Date] ) }, ''[Value] ) )
    VAR __LastYear =  YEAR ( MAXX ( { MAX ( 'POS Aggregate'[Week End Date] ) }, ''[Value] ) )
    RETURN CALENDAR (
        DATE ( __FirstYear, 1, 1 ),
        DATE ( __LastYear, 12, 31 )
    )
VAR __Step3 =
    GENERATE (
        __Calendar,
        VAR __IsStandardLocale = IF ( FORMAT( DATE( 2000, 1, 1 ), "oooo" ) = "oooo", TRUE, FALSE )
        VAR __MonthFormatString = IF( __IsStandardLocale, "mmm", "ooo" )
        VAR __DayFormatString = IF( __IsStandardLocale, "ddd", "aaa" )
        VAR __LastTransactionDate = MAXX ( { MAX ( 'POS Aggregate'[Week End Date] ) }, ''[Value] )
        VAR __Date = [Date]
        VAR __YearNumber = YEAR ( __Date )
        VAR __QuarterNumber = QUARTER ( __Date )
        VAR __YearQuarterNumber = CONVERT ( __YearNumber * 4 + __QuarterNumber - 1, INTEGER )
        VAR __MonthNumber = MONTH ( __Date )
        VAR __WeekDayNumber = WEEKDAY ( __Date, __WeekDayCalculationType )
        VAR __WeekNumber = WEEKNUM ( __Date )
        VAR __WeekNumberYear = WEEKNUM ( __Date, 2 )
        VAR __WeekDay = FORMAT ( __Date, __DayFormatString )
        VAR __WeekEndDate = __Date - MOD ( __Date + 0, 7 ) + 7
        RETURN ROW (
            "Year", __YearNumber,
            "Year Quarter Number", __YearQuarterNumber,
            "Year Quarter", FORMAT ( __QuarterNumber, "\Q0" ) & "-" & FORMAT ( __YearNumber, "0000" ),
            "Quarter", FORMAT( __QuarterNumber, "\Q0" ),
            "Year Month", FORMAT ( __Date, __MonthFormatString & " yyyy" ),
            "Year Month Number", __YearNumber * 12 + __MonthNumber - 1,
            "Month", FORMAT ( __Date, __MonthFormatString ),
            "Month Number", __MonthNumber,
            "Week Number", __WeekNumber,
            "Week Number Year", __WeekNumberYear,  // Adding Week Number Year
            "Day of Week Number", __WeekDayNumber,
            "Day of Week", __WeekDay,
            "DateWithTransactions", __Date <= __LastTransactionDate,
            "Week End Date", __WeekEndDate
        )
    )
RETURN
    __Step3
Need Help
  • audreygerred's avatar
    audreygerred
    2 years ago

    Right now, it just looks like your chart is sorting by the value (greatest to least) instead of the week number. As long as your week number is set as a number and not a string, click the three dots on your chart, and click sort axis, then select week number. 

     

    As far as your error goes, it is implying that for at least 1 week number, there are multiple different values for that same week in Week Number Year.

     

1 Reply

  • audreygerred's avatar
    audreygerred
    Super User
    2 years ago

    Right now, it just looks like your chart is sorting by the value (greatest to least) instead of the week number. As long as your week number is set as a number and not a string, click the three dots on your chart, and click sort axis, then select week number. 

     

    As far as your error goes, it is implying that for at least 1 week number, there are multiple different values for that same week in Week Number Year.