Forum Discussion

Advocate IV

9 years ago

Last 'x' weeks with custom week format

Hi, I need a boolean calculatation field to filter out the last 6 weeks of my date in oder to make a yoy comparison. However, I have a bespoke week format based on the week starting on Monday ...

BetterCallFrank

Resolver IV

9 years ago

How about

Last6Weeks =
  IF( WEEKNUM(TODAY(), 21) - Weeknum(Date, 21) <= 6,
      TRUE,
      FALSE
  )

Advocate IV

9 years ago

Unfortunately this does not work as Weeknum is treated as a number and not date. So for example, Week 2 will always be seen as inferior to week 52 by a 50 margin etc.

BetterCallFrank
Resolver IV
9 years ago
well, of course such cases need to be considered if you do time intelligence manually - but it can be achieved easily, e.g.
Last6Weeks =
VAR WkToday = WEEKNUM( TODAY(), 21 )
VAR WkDate = WEEKNUM( Date[Date], 21 )
VAR SameYear = YEAR(TODAY()) = YEAR(Date[Date])
RETURN
IF(
SameYear,
WkToday - WkDate <= 6,
WkToday + 52 - WkDate <= 6
)
probably not the most elegant approach, but just to give you an idea.

HTH,
Frank
- MarcF
  Advocate IV
  9 years ago
  Thanks that's a first working solution. However, I now have to find a way to rank the weeks on my bar chart from newest first (ie: 2,52,51 etc.). Would you have any idea on how to achieve that given that I need to compare weeks year-on-year? I was hoping there would be a way to get DAX intelligence time to work on custom weeks.
  - v-sihou-msft
    Microsoft Employee
    9 years ago
    MarcF
    
    In DAX, there's no function for week level time intelligence, you have to custom your week based calendar, then build the week baesd time intelligence logic manually as BetterCallFrank suggested.
    
    Please refer to link below:
    
    Week-Based Time Intelligence in DAX
    
    Regards,