Week Indexing on a DAX created Date Table

Question

I want to have a date table created with DAX for a power BI report where it will have week index numbers 0,-1,-2,etc to go back in time and make comparisons of weeks. I found the below out on the web (I dont remember where)

    DATE = 
  GENERATE (
    CALENDAR( DATE( YEAR( TODAY() ) - 2, MONTH( TODAY() ), DAY( TODAY()) ), TODAY()),
    VAR startOfWeek = 1 // Where 1 is Sunday and 7 is Saturday, thus a 3 would be Tuesday    
    VAR currentDay = [Date]
    VAR days = DAY( currentDay )
    VAR months = MONTH ( currentDay )
    VAR years = YEAR ( currentDay )
    VAR nowYear = YEAR( TODAY() )
    VAR nowMonth = MONTH( TODAY() )
    VAR dayIndex = DATEDIFF( currentDay, TODAY(), DAY) * -1
    VAR todayNum = WEEKDAY( TODAY() )
    VAR weekIndex = INT( ROUNDDOWN( ( dayIndex + -1 * IF( todayNum + startOfWeek <= 6, todayNum + startOfWeek, todayNum + startOfWeek - 7 )) / 7, 0 ) )
  RETURN ROW (
    "day", days,
    "month", months,
    "year", years,
    "day index", dayIndex,
    "week index", weekIndex,
    "month index", INT( (years - nowYear ) * 12 + months - nowMonth ),
    "year index", INT( years - nowYear )
  )
)

This report should be updated daily but I noticed that today VAR Week index is not calculating correctly. Today is 4/1/2020 so I would expect 3/29/2020 - 4/1/2020 to have a week index of 0 however only 4/1/2020 and 3/31/2020 have week index 0.

I have played around with this formula a bit and cannot seem to get something that would consistently work for a report that is updated daily. I am probably going to just begin using the ISO week and ISO year values to get at my comparisons but I will lose some functionality that the week index number gives me. Can anyone please help with the a working formula?

Thank you!

Aldert Aldert · Accepted Answer · 2020-04-01T18:13:51

The weekIndex can be simpler calculated, you can change it to:

VAR weekIndex = ROUNDDOWN((dayIndex + todayNum - startOfWeek - 6)/7;0)

Week Indexing on a DAX created Date Table

1 Answers