Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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.
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,
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |