Last 'x' weeks with custom week format

[MarcF]

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 (ie: Weeknum(Date, 21)). I am struggling to filter last 'x' weeks as the weeks run from 1 to 52. The DATEDIFF function based on the standard 'WEEK' interval does not do the trick due to the different week format that I use.

Thank you

[BetterCallFrank]

How about

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

[MarcF]

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]

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]

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]

@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,