Week-based measure containing weeks in two years.

[iDataDrew]

Hi,

I need to create a measure like the following: Total sales by week for the last 26 weeks.

Showing total sales by week of year is simple enough.  But showing it for the last 26 weeks is a little trickier.  Especially if you're in week 10 of the current year (for example), a simple subtraction of weeks doesn't seem like it would work, because you're going into the previous year at that point.  The x-axis of the chart would need to look something like this, if the current week of year was the 10th week:

37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 1 2 3 4 5 6 7 8 9 10

Does that make sense?  Any insight on how this measure would need to be constructed?  Thank you.

[Eric_Zhang]

@iDataDrew

How about using a calendar table with "weeksequence"? In the measure, you could use some filter like [weeksequence]>= MAX([weeksequence])-10.

Calendar =
VAR basicCalender =
    ADDCOLUMNS (
        ADDCOLUMNS (
            CALENDAR ( "2016-01-01", "2017-12-31" ),
            "WeekNo", WEEKNUM ( [Date] )
        ),
        "YearWeek", YEAR ( [Date] ) * 100
            + [WeekNo]
    )
RETURN
    ADDCOLUMNS (
        basicCalender,
        "WeekSequece", RANKX ( basicCalender, [YearWeek],, ASC, DENSE )
    )

[Phil_Seamark]

Hi there. Does your axis have to show the week number or could it show a date? Eg week sataring or week finishing?

[iDataDrew]

@Phil_Seamark, either way should be fine.

[Phil_Seamark]

ok, cool.

Do you have a small sample set of data?  And are you using a separate Date/Calendar table?

[chbraun]

Hi,

I typically use the "Sort by" feature in cases like this and define a sorting column like this (where "date" is your date column):

weekSorter = (Year(date) - 2016) * 53 + Week(date) 

This gives you a continuous count of weeks across years which you can use to sort weeks in your charts.

Hope this helps!