Comparing Same Week Prior Year - Weeknum seems off

[bigck2]

Hello,

I have a data model with two relevant tables: BoxScore and DimCalendar. My DimCalendar table is set up as a date table, and included in that table I have a field called 'WeekNumber' which is defined with the excel formula: = WEEKNUM( [@Date], 1 ).

My BoxScore table has a bunch of metrics which are measured weekly. There is a field 'End Date' which is linked to the DimCalendar table on the 'Date' field.

I have a PivotTable report set up, and it seems to be acting strange. I have Rows set as 'WeekNumber' and Value with a calculated field 'Total Shows' (which is just =SUM ( BoxScore[Shows] ) ). I have another calculated field, 'PY Total Shows' which is defined as =CALCULATE( [Total Shows] , SAMEPERIODLASTYEAR( DimCalendar[Date] ) ).

It seems strange because week 1 for 2016 should end on 01/02/2016, and week 1 for 2015 should end on 01/03/2015.

When these are supposed to line up next to each other on the report, in week 1 there is a value for 2016, but the space for 'PY Total Shows' is blank. WeekNumber 2 has the quantity for the week ending on 01/03/2015 in the 'PY Total Shows' measure.

It seems strange. Please let me know if you have any suggestions or resources.

Thanks,

Chris

 

[bigck2]

Here is a screenshot of what I'm talking about.

Total Shows for Week #1 for current year is 241. This correctly corresponds to the week ending on 01/02/2016.

Total Shows for Week #1 for Prior Year is blank. Then for Week #2 PY Total Shows has 477 Shows. That number corresponds to the week ending on 01/03/2015. But that should be Week #1. From my formula WeekNum = WEEKNUM ( "01/03/2015" ) = 1.

 

[akice]

Here is an article by Marco Russo & Alberto Ferrari that may be of help:
https://www.sqlbi.com/articles/week-based-time-intelligence-in-dax/