Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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 ) )
ok, cool.
Do you have a small sample set of data? And are you using a separate Date/Calendar table?
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!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |