Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MarcF
Frequent Visitor

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

5 REPLIES 5
BetterCallFrank
Resolver IV
Resolver IV

How about

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

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.

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

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.

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.