Calculate the finance year/week exactly 52 weeks ago

[thorpyuk]

Hi all,

I've created a measure to pull the finance year exactly 52 weeks ago, but although it evaluates ok, it's only showing me this financial year not last. The reason for this, is because our financial year is being extended to 66 weeks, so week-on-week comparisons are useless! I'll be editing this code to show the weekNo exactly 52 weeks ago too:

TestYear = CALCULATE( MAX('db_v_BaseCalendar_TYLY'[FinYear] ) , FILTER('db_v_BaseCalendar_TYLY', MAX('db_v_BaseCalendar_TYLY'[TransDate]) - (7 * 52))
)

Please can someone see what i'm missing? Thanks all!

[amitchandak · Accepted solution]

@thorpyuk , Not very clear. need example.

See these example with date table and time intelligence

measure

52 week behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-364,Day))

Week need week Rank for analysis

new column

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)

measure
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))

Refer : https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

Any Weekday Week - Start From Any day of Week
https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Powe...

[thorpyuk]

Thanks amitchandak, the first one (measure) was what i was after (with some modification) - this is what i did:

CALCULATE( MAX('db_v_BaseCalendar_TYLY'[FinYear] ) , DATEADD('db_v_BaseCalendar_TYLY'[TransDate] , -(52 * 7) , DAY ))