Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 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

    Phaneendra Adusumilli

  • 2.  RE: DAX

    Posted Jul 11, 2022 02:36 PM

    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())
    '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.


    Adam Artur Boltryk
    Business Analyst