Forum Discussion

zedsdead's avatar
zedsdead
Regular Visitor
7 months ago
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...
Date
DAX
Max
min
  • cengizhanarslan's avatar
    cengizhanarslan
    7 months ago

    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 )

     

  • FreemanZ's avatar
    FreemanZ
    7 months ago

    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:

     

Zanqueta's avatar
Zanqueta
Icon for Super User rankSuper User
7 months ago

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)
)