topic Re: Rolling Date Identifiers in DAX Commands and Tips

Rolling Date Identifiers

zedsdead — Mon, 26 Jan 2026 18:58:34 GMT

I support a team monitoring weekly captured data from a 5 week window. Each row of my set has a specific date - thousands of rows of data fitting into 5 weeks, and my report needs to dynamically compare each week's data with the others. I'm working to prep DAX to assign number 1-5 to each of the weeks as the project rolls on.

So currently: Dec 22 = 1, Dec 29 = 2, Jan 5 =3, Jan 12 = 4, Jan 19 = 5.

Next week: Dec 29 = 1, Jan 5 = 2, Jan 12 = 3, Jan 19 = 4, Jan 26 = 5 and so on..

First time in the forums so please let me know if I need to provide additional context.

Re: Rolling Date Identifiers

Zanqueta — Mon, 26 Jan 2026 20:00:34 GMT

Hi zedsdead,

I am not entirely certain, but for this type of dynamic comparison between weeks, what usually works very well is to create a “Week Index” logic that adjusts itself automatically as the calendar moves forward.

From your description, your requirements are:

You always work with a 5‑week rolling window.
Each of those 5 weeks must be assigned a label from 1 to 5, where:
- 1 = oldest week in the current 5‑week window
- 5 = most recent week in the current 5‑week window
When a new week is added, every existing week “shifts” its index.

That´s it ?

My suggestion is to create a measure that calculates, for each week, which index (1–5) it should have based on the most recent week present in the data.

Ideally, you should have a Date/Calendar table and, within it, a column that represents the week. Typical options include:

A “week ending” date (e.g. every Friday or Sunday),
Or a week number plus year (e.g. ISO week).

For the sake of the example, let us assume you have:

'Data'[WeekEnding]

This is a date representing the end of each week (for example, every Friday).

WeekIndex measure:

WeekIndex :=
VAR LatestWeek =
CALCULATE(
MAX('Data'[WeekEnding]),
ALL('Data')
)
VAR ThisWeek =
SELECTEDVALUE('Data'[WeekEnding])
RETURN
IF(
NOT ISBLANK(ThisWeek),
5 - DATEDIFF(ThisWeek, LatestWeek, WEEK)
)

Re: Rolling Date Identifiers

cengizhanarslan — Mon, 26 Jan 2026 20:45:53 GMT

1) Create a WeekStart column in your Date table (Monday-start example):

WeekStart = 'Date'[Date] - WEEKDAY('Date'[Date], 2) + 1

2) Then create a RollingWeekIndex (1–5) measure (or calculated column if you truly need it stored):

RollingWeekIndex = VAR CurrentWeekStart = MAX ( 'Date'[WeekStart] ) VAR LatestWeekStart = CALCULATE ( MAX ( 'Date'[WeekStart] ), ALL ( 'Date' ) ) VAR DiffWeeks = DATEDIFF ( CurrentWeekStart, LatestWeekStart, WEEK ) RETURN IF ( DiffWeeks >= 0 && DiffWeeks <= 4, 5 - DiffWeeks )

Re: Rolling Date Identifiers

FreemanZ — Tue, 27 Jan 2026 08:03:49 GMT

hi zedsdead ,

Not sure if i fully get you, try to write a calculated column like:

Column = VAR _DateCurrent = TODAY() // VAR _DateCurrent = DATE(2026, 2, 9) VAR _MondayCurrent = _DateCurrent - WEEKDAY(_DateCurrent, 3) VAR _WeekShift = DATEDIFF(DATE(2025,12,22), TODAY(), WEEK) VAR _result = _WeekShift - DATEDIFF([date], _MondayCurrent, WEEK) +1 RETURN _result

it works like below:

you may comment line 2 and uncomment line 3 to try future dates, like next week:

Re: Rolling Date Identifiers

v-ssriganesh — Fri, 30 Jan 2026 10:44:42 GMT

Hello zedsdead,
Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to FreemanZ, cengizhanarslan & Zanqueta for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.

Thank you for being part of the Microsoft Fabric Community.

Re: Rolling Date Identifiers

zedsdead — Fri, 30 Jan 2026 13:12:37 GMT

thank you team for all of the responses! I'm still working through the solution and will mark as resolved asap.

Re: Rolling Date Identifiers

v-ssriganesh — Mon, 02 Feb 2026 06:44:19 GMT

Hello zedsdead,

Hope everything’s going great with you. Just checking in has the issue been resolved or are you still running into problems? Sharing an update can really help others facing the same thing.

Thank you.