Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago

YOY Comparison when only weekly info is available

My data is by week, not by day. I have a table of week ending dates / week number / Month / Year etc. I have a combined YEARWK field that matches the year and week - so the most recent full week is 201627.

the dollar table has a matching YearWk field. This year is 201627, last year 201527, etc.

 

By filtering the week numbers I can get YTD to show, but can't figure out how to get the change %.

Every tip I have found is based on daily amounts. Any hints????

 

Need Help

3 Replies

  • v-sihou-msft's avatar
    v-sihou-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    10 years ago

     

    Anonymous

     

    In DAX, there's no direct method to do YOY comparison on Week, which means PREVIOUSYEAR() function can't work for weeks. In this scenario, I assume your YearWeek column is a text column. You can substring the year part and minus 1 to get the last year, then concatenate into PreviousYear Week and lookup the corresponding value. Please see my sample below:

     

    Create a calcualted column to get PreviousYear Week.

    PreviousYear Week = CONCATENATE(left(Table8[YearWeek],4)-1,RIGHT(Table8[YearWeek],2))

    Create a measure the get the PreviousYear Week Value.

    PreviousYear Week Value = 
    IF(CONTAINS(Table8,Table8[YearWeek],Table8[PreviousYear Week]),
    LOOKUPVALUE(Table8[Value],Table8[YearWeek],Table8[PreviousYear Week]),
    BLANK())

    Regards,

    • Anonymous's avatar
      Anonymous
      Not applicable
      10 years ago

      I must be doing something wrong!! I am getting: Column 'PrevousYear Week' in table 'Blended' cannot be found or may not be used in this expression.

      The column was added and does show when I put it on a view.  Can you tell from the screen print below what I did wrong???

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        10 years ago

        Simon_Hou,

        thanks but I guess I didn't explain myself well enough.

         

        This is a sample of the date table:

         

        And this is a sample the data file:

         

        Ideally what I want is a YOY % change column for both the current week and the Year to date.

        Since I have both year and week available, I have no problem showing same week sales,  what I can't do is get a % change.

         

        Let's say this is the current week data:

         

        this is what I would want to see for the change % for the week.

         

        Appreciate any guidance you can give!!! Thanks!