Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
iDataDrew
Advocate IV
Advocate IV

Week-based measure containing weeks in two years.

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.

5 REPLIES 5
Eric_Zhang
Employee
Employee

@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 )
    )

 

 

Capture.PNG

Phil_Seamark
Employee
Employee

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

@Phil_Seamark, either way should be fine.

ok, cool.

 

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

chbraun
Helper I
Helper I

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.