SAMEPERIOD LAST Year behaving different by month and week

[zanottiluca]

hi expert, 

I may need your expertise as I have encounter an odd scenario. 
I am using SAMEPERIODLASTYEAR and by comparing month by month it is behaving perfectly (image below). 
The issue happens when I put the week, where the number does not make sense. 
Calendar is formatted as a Date table. 
Dax I am using is this one: 

Visits LY = CALCULATE([# of Cases], SAMEPERIODLASTYEAR('Calendar'[Dates]))

Any help would be greatly appreciated. 
Many thanks in advance

Image 1


image 2

[Fowmy · Accepted solution]

@zanottiluca 

Try this meaure, however, weeks are different from year to year sometimes:

Visits LY = 
VAR __PreYear = SELECTEDVALUE('Calendar'[Year]) - 1
VAR __PreWeekNum = SELECTEDVALUE('Calendar'[Week Number])
VAR __Result = 
CALCULATE(
    [# of Cases],
    'Calendar'[Year] = __PreYear,
    'Calendar'[Week Number] = __PreWeekNum
)
RETURN
    __Result

[Fowmy · Accepted solution]

@zanottiluca 

Try this meaure, however, weeks are different from year to year sometimes:

Visits LY = 
VAR __PreYear = SELECTEDVALUE('Calendar'[Year]) - 1
VAR __PreWeekNum = SELECTEDVALUE('Calendar'[Week Number])
VAR __Result = 
CALCULATE(
    [# of Cases],
    'Calendar'[Year] = __PreYear,
    'Calendar'[Week Number] = __PreWeekNum
)
RETURN
    __Result

[zanottiluca · Accepted solution]

thanks @Fowmy  this has solved the issue and also (for future reader) I have been able to put in a drill through  line chart (upper level Month and lower level weeknumber) either measure by leveraging the below:
basically the %difference 2 use the above formula while the second uses the SAMEPERIODLASTYEAR  

Difference by time select = IF(ISINSCOPE('Calendar'[WeekNumber]),[% difference2],[% difference])

[gmsamborn]

Hi @zanottiluca 

DAX Time Intelligence functions are meant to work with YEAR, QUARTER, MONTH, and DAY.  WEEK isn't handled.

The reason for this is Week 1 of 2023 doesn't necessarily correspond with Week 1 of 2022.

For example, (with weeks starting on Sunday) 2024/01/01  is a Sunday so it would be Week 1.  On the other hand, 2023/01/01 is a Saturday and Week 1 so 2023/01/02 is a Sunday and a new week (Week 2).

These inconsistencies exist for every year because 7 * 12 = 364, not 365.  Also, not all years start on a Sunday.  Some years are more obvious than others.

Whenever I am working with weeks, I use a date table I have that was written in 'M' - Power Query  (I didn't write it but I have used it regularly. )  It includes the following week-related columns:

Week of Year
Week of Month
Start of Week
End of Week
Yr-Week
Yr-Week number
Week Offset

This date table makes handling weeks easier but does not handle the inconsistencies noted above.

I hope this makes sense.

SPLY - M dimDate.pbix