Hi Phaneendra,
Leave this calculation of week numbers aside.
1) I assume, you have your calendar in DIM table. If not, it's mistake, you have to fix in first instance.
2) You have to create separate column YearWK with year and week, best as a number and I have always format YYYYMM -> 202201 202202 etc.It's number and working good, and most important - when you sort it it's always sort correctly.
3) Create column with RANK - eg. 'Week Rank' ->
RANKX('Calendar', 'Calendar'[YearWK], 'Calendar'[YearWK], asc, dense)
4) Create column RELATIVE
Var currentweek = LOOKUPVALUE('Calendar'[Rank], 'Calendar'[Dates], TODAY())
Return
'Calendar'[Rank] - currentweek
Now just use in measures filters based on value on this column, remember CURRENT week is always 0. Last week -1, 2 weeks ago -2 etc. In same time weeks in future are positive numbers: next week is 1, 2 weeks ahead is 2 etc.
Regards,
------------------------------
Adam Artur Boltryk
Business Analyst
------------------------------
Original Message:
Sent: Jul 08, 2022 11:59 AM
From: Phaneendra Adusumilli
Subject: DAX
I have a table like below now
I want show current week -1, current week -2, current week -3, current week -4 that means in new column I want to show if WeekEnding date is (Sunday, July 11,2022) then current week -1 should show the values of (Sunday, July 03,2022) in (Sunday, July 11,2022) as new column as current week -1. I created a measure like this
Current Week -1 Traffic = CALCULATE(SUM(KMPTraffic[KMP Count]),FILTER(KMPTraffic,YEAR(KMPTraffic[date])=YEAR(NOW())&&WEEKNUM(NOW())-1=KMPTraffic[WeekNumber]))
but it giving values for only for that particular weekending that is Sunday,July 03,2022 and giving blank values for remaining weekend. So I want to show values for remaining weekend also. How to write DAX for this
------------------------------
Phaneendra Adusumilli
------------------------------