# Power BI Exchange

View Only

## DAX

• #### 1.  DAX

Bronze Contributor
Posted Jul 08, 2022 12:00 PM
Edited by Phaneendra Adusumilli Jul 08, 2022 01:52 PM
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

------------------------------
------------------------------

• #### 2.  RE: DAX

Posted 30 days ago

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,

------------------------------