Forum Discussion

MarcF's avatar
MarcF
Icon for Advocate IV rankAdvocate 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 (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

Need Help
Show and Tell

5 Replies

  • BetterCallFrank's avatar
    BetterCallFrank
    Icon for Resolver IV rankResolver IV
    9 years ago

    How about

    Last6Weeks =
  IF( WEEKNUM(TODAY(), 21) - Weeknum(Date, 21) <= 6,
      TRUE,
      FALSE
  )
    • MarcF's avatar
      MarcF
      Icon for Advocate IV rankAdvocate 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's avatar
        BetterCallFrank
        Icon for Resolver IV rankResolver 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