Forum Discussion
Week comparison same week last year
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
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,
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