Calculate dynamically Week Differnce

[Anonymous]

Hi ,

I need dax formula to calculate Target column , which differnce of current week and next week .

Quarter	Year-Week	Diff	Target
Q2'20	2020-26	8664	8664
Q2'21	2020-27	0	8664
Q2'22	2020-28	0	8664
Q2'23	2020-29	1408	7256
Q2'24	2020-30	0	7256
Q2'25	2020-31	672	6584
Q2'26	2020-32	0	6584
Q2'27	2020-33	0	6584
Q2'28	2020-34	370	6214
Q2'39	2020-45	1049	3748

Thanks

[amitchandak]

@Anonymous , preferably move week -year to separate table and create a rank on that

Week Rank = Rankx(all(Week],[Week Year],,asc,dense)

Now use this rank for next and previous week

This Week = CALCULATE(sum('Table'[Target]), FILTER(ALL('WEEK'),'WEEK'[Week Rank]=max('WEEK'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Target]), FILTER(ALL('WEEK'),'WEEK'[Week Rank]=max('WEEK'[Week Rank])-1))
Next Week = CALCULATE(sum('Table'[Target]), FILTER(ALL('WEEK'),'WEEK'[Week Rank]=max('WEEK'[Week Rank])-1))

refer my blog on Week

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123