LY Measure by week number

[BrianForth]

I'm having trouble pulling a LY measure for my fact table. I've checked a bunch of solutions on the forum, and tried the answers (SAMEPERIODLASTYEAR, DATEADD, etc.), but I'm not having any luck. My current measure defintion (below) appears to be giving this year's results, but only for the week of 12/31/2023, which I think is due to the fact that that week contains dates predominantly from 2024. I can think of two issues with my data that are pretty nonstandard that may be contributing: first, the data comes in weekly, and the client wants it by week number, so an entry from 1/7/2023 would be week 1, but 1/7/2024 would be week 2. The second potential issue is that the client also wants Totals to the left, so this is a calculated table with some peculiarities in that capacity, as well. The 'Dates' table is a date table with 1:* relationship to 'ProdMatrix'

Utilization (LY) =

VAR _lastyear = MAX(Dates[Year])-1

VAR _thisweek = MAX(Dates[Week of Year])

RETURN

CALCULATE(

    DIVIDE(SUM(ProdMatrix[Actual Billable Hours]),

        SUM(ProdMatrix[TotalHours]),0),

Dates[Year] = _lastyear, Dates[Week of Year]=_thisweek)

Sample File 

[_AAndrade]

I didn't see it. I'm analysing your pbix file and if I'm seeing well in 2023 our data starts in week 8 and in 2024 you don't have data to this week.
So what result you expected with your formula? I write a simple measure to try find the actual hours of last year week and seems to works fine.

Result:

2023 Actual Hours:

[BrianForth]

I tried your solution on my end, and it looks like the issue breaks down when using the "Week Starting" field, which causes LY results to be posted to LY dates:

[_AAndrade · Accepted solution]

In my example i'm using the dates from Dates table and not from ProdMatrix table. That could be the problem.
If you need the starting date one your visual, add this column on Dates table.
In my examples I don't have data to 2022.

[BrianForth]

It took some doing in Power Query to add the "Week Starting" column to the Dates table, since it's using that "Total" value in order to fake putting the total column to the left of the matrix, but I have done it!

Here's the breakdown of some data:

It looks like it's getting the proper results based on this information. Now I just need to figure out what data is being captured in the "Total" Utilization for LY.

Thanks for your help through this one!

[_AAndrade]

Do you have a calendar table in your model? It's much easier to calculate dates with a calendar table.

[BrianForth]

Yes, the 'Dates' table is a calendar table.

[_AAndrade]

If you already tried Sameperiodlastyear and Dateadd without any  success it's difficult to help you more without analysing your pbix.

[BrianForth]

I thought it might be, which is why I included the sample file in my original post.