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 comp...
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:

     

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.