Forum Discussion

zedsdead's avatar
zedsdead
Regular Visitor
6 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 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. 

Date
DAX
Max
min
  • cengizhanarslan's avatar
    cengizhanarslan
    6 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
    6 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:

     

6 Replies

  • v-ssriganesh's avatar
    v-ssriganesh
    Community Support
    6 months ago

    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.

  • v-ssriganesh's avatar
    v-ssriganesh
    Community Support
    6 months ago

    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.

    • zedsdead's avatar
      zedsdead
      Regular Visitor
      6 months ago

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

  • FreemanZ's avatar
    FreemanZ
    Super User
    6 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:

     

  • cengizhanarslan's avatar
    cengizhanarslan
    Super User
    6 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 )

     

  • Zanqueta's avatar
    Zanqueta
    Super User
    6 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)
    )