Week comparison same week last year

[maurcoll]

Hi i need to create the below table the issue i have is that the week isnt bringing back the same week number last year it is bringing back the same date range.
For example for week 11-17 Nov 24 i want the data for the 13-19 Nov 23 to compare i currently get the data for 11-17 Nov 23. In the example below the data showing for last year week 18/11/24 is the total i need to show on the same row as the 11/11/24.

Start of Week	Total Sales	Last Year
11/11/24	3499	4044
18/11/24	3470	4038

this is the measure i am currently using, i've also tried with same period last year and get the same results

Total Sales Same Week last Year =

var currentweek = SELECTEDVALUE('Calendar'[Week of Year])

VAR CurrentYear = SELECTEDVALUE('Calendar'[Year])

RETURN

CALCULATE([Total Sales],

FILTER(ALL('Calendar'),

'Calendar'[Week of Year] = currentweek && 'Calendar'[Year] = CurrentYear -1)

)

[v-pgoloju · Accepted solution]

Hi @maurcoll,

Try this checks to fix the issue

If your current WeekIndex is, for example, 210, but there’s no data available for index 158 (210 - 52), then the CALCULATE function won’t find a match — resulting in a blank output.

Test Last Year WeekIndex = SELECTEDVALUE('Calendar'[WeekIndex]) - 52

Now, place the following in a table visual: 'Calendar'[Start of Week],'Calendar'[WeekIndex]

Is WeekIndex a whole number?
Is 'Start of Week' unique per row?
Is the relationship on 'Date', not 'Start of Week'?
Are you using MAX() instead of SELECTEDVALUE() in visuals?
Does WeekIndex - 52 exist in your calendar table?

Thank you & Regards,
Prasanna kumar

[v-pgoloju]

Hi @maurcoll,

Thank you for reaching out to the Microsoft Fabric Forum Community.

Also, a special thanks to @lbendlin for the quick and helpful response.

In addition to the solution provided by the super user, here's an alternate approach you can try:

You can create a custom "Week Index" with the following DAX expression:

WeekIndex =
RANKX(
FILTER('Calendar', NOT(ISBLANK('Calendar'[Start of Week]))),
'Calendar'[Start of Week],
,
ASC
)

Then, use this measure for Total Sales Last Year (TSLY):

TSLY =
VAR CurrentWeekIndex = SELECTEDVALUE('Calendar'[WeekIndex])
RETURN
CALCULATE(
[Total Sales],
FILTER(
ALL('Calendar'),
'Calendar'[WeekIndex] = CurrentWeekIndex - 52
)
)
If the issue persists, feel free to provide more details, and we'll be happy to assist further.

If you find this response helpful, please consider marking it as the accepted solution and giving a thumbs-up to support others in the community.

Thank you and regards,

[maurcoll]

Hi

Thank you for the above unfotunately i get a blank result when i put into a table with the start of week in.
I have created the start of week column in my calendar table by making a copy of the date column and changing to start of week  using

. It is stored as a date field. Not sure if this is impacting

[lbendlin]

If you truly want to compare the same weeks between current and previous year then your only option is to subtract 364 from the date.  That is the only true correct comparison.