Last Week of Fiscal Year Where Nonsequential

[rzrbkpk]

I have a query that provides the last week of a field(Fiscal Week) in Year-Week format. For example, if the current week is 202452 then the previous week is 202451. I'm currently accomplishing this with the formula Max(Fiscal Week)-1 in the query field. I've run into an issue though when the new fiscal year begins. Now if the current week is 202501, my formula returns 202500, which is not a valid week. I want it to return 202452. Not sure how to accomplish this.

[Welshgasman]

I would write my own function to do this.
Then if 202501, one can check for 01 and make that 52 and subtract 1 from year.

[jojowhite]

also, if the field [Fiscal Week] is a Date field, you just subtract 1 week from it, eg:

PrevWeek: Format$([Fiscal Week]-7,"yyyy") & Format$(Format$([Fiscal Week]-7,"ww"),"00")

[madpiet]

Depending on how often you do time series analysis... it might be worth your while to create a Calendar table. Then you can compare year over year etc. But most of the time, you'd do that in Excel and maybe use DAX (which is a PITA... once you get your model right, you can do crazy stuff with it, but DAX is not easy.)

[jojowhite]

If in case your [Fiscal Week] is Numeric (as in your post #1, deducting 1 resulted in 202500,
which is Not a Valid year/week).

PrevMonth: DMax("[Fiscal Week]","YourTableName","[Fiscal Week] < " & [Fiscal Week])