Forum Discussion
Solved
Rolling Date Identifiers
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 comp...
1) Create a WeekStart column in your Date table (Monday-start example):
WeekStart = 'Date'[Date] - WEEKDAY('Date'[Date], 2) + 12) 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 )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 _resultit works like below:
you may comment line 2 and uncomment line 3 to try future dates, like next week:
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: