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 ...
Need Help
Show and Tell
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

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