Week Numbers to Wave Reference Numbers

[Jacqee]

I'm reporting on account inventory based on when the account renews.  Most accounts renew 1/1.  (Note: most accounts have more than one item they are renewing so I'm rolling the account up to the last received date.)  We begin receiving renewal information 13 to 14 weeks prior to the 1/1 date or last week of September/beginning of October.  I'd like to begin counting weeks that we receive the renewal account information with Wave 0 being anything received prior to week 39 for 1/1/24.   Wave 1 being week 39, Wave 2 = 2 week 40, Wave 3 = week 41 etc. 

Using 1/1/24 as the example, week 1 of 1/1/25 would be Wave 15 for the 1/1/24 renewal.  Week 2 of 1/1/25 = wave 16 and so on up to about Wave 27 or 28.  Beyond this, I don't need to track it weekly…or haven't when doing it manually.  But would be appropriate to either add Waves or put them in a Wave+ type category....not sure how to accomplish this.

In the date table, it appears to work however when putting the count of accounts by Waves in a visual, an account received early for a 1/1/24 renewal and also anything received late both show in the Wave 0 category.  This is highlighted for me by an account that was received 7/4/24 for a 1/1/24 renewal and shows as a Wave 0 like it was received prior to the 1/1/24 renewal date rather than after the 1/1/24 renewal date. 

**What I'd like is to be able to let the user choose the renewal date whether it's a 1/1/YY date or a 7/1/YY date and have the Wave numbers automatically adjust so that Wave 0 is 14 weeks prior to the renewal date chosen and such as previously discribed.

Here is the Date table I created to try to convert to the waves.  I may have a week 53 issue too if there are suggestions for that.  I also don't love the idea that I have to update this table Date for any renewal date. 

I also tried to do a calculated column but it didn't work any better: 

Last Recvd Wk # = WEEKNUM(DataView[Last Acct Received],1)

I'm open to any suggested solutions.

Thank you!!!

[aduguid]

Try this measure. It allows the end user to select the renewal date.

Wave Number = 
VAR RenewalDate = SELECTEDVALUE('Renewal Date'[Date]) // get the selected renewal date from the user
VAR WeekNumReceived = WEEKNUM(DataView[Last Acct Received], 1)
VAR WeekNumRenewal = WEEKNUM(RenewalDate, 1)
VAR YearReceived = YEAR(DataView[Last Acct Received])
VAR YearRenewal = YEAR(RenewalDate)
VAR WaveCalculation = 
    IF(
        YearReceived < YearRenewal,
        WeekNumReceived - WeekNumRenewal,
        WeekNumReceived + (53 - WeekNumRenewal) -- For weeks after the renewal date
    )
RETURN
    IF(WaveCalculation <= 0, 0, WaveCalculation)

[Jacqee]

Thank you for your suggestion!!  I've tried it by copying it in and by keying it in.  For VAR 2-5 I'm receiving "Cannot find name".  If it just hit enter and let it try, the full message is: "A signle value for column 'Renewal Date' in table DataView cannot be determined.  This happens when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, sum or count to get a single result."

If I add 'selectedvalue' to VAR 2-5 like the first VAR, the measure is accepted with no errors but the values are all 53 or higher.  I figure adding selectedvalue is incorrect but wasn't sure what else to try.  

Any additional guidance you can provide would be greatly accepted!