Forum Discussion

dustdaniel's avatar
dustdaniel
Icon for Helper II rankHelper II
1 year ago
Solved

Year Over Year Increase% by week

Hi there,

 

I have a matrix with year in columns and month / day of month on rows. I noticed that when filtering by week # there was a difference on my YoY % measure and I understood this is because these are having different considerations.

 

But I still need to compare YoY % perfomance  per week, I tried the solution indicated in this post but I couldn't make it work properly in my report.

 

The measure as a resault indicates 36.51% (in a Card) for week 28 which is correct but I'm not able to display it on the matrix using Month and days on Rows

 

 

Please find here a my data sample. 

 

Should I create a different matrix only to display weekly performance comparison?

Need Help
Tutorial Requests
  • v-hashadapu's avatar
    v-hashadapu
    1 year ago

    Hi dustdaniel , Thank you for reaching out to the Microsoft Community Forum.

     

    Based on your scenario, we created a DAX measure and worked out an answer getting the same output in both card and Matrix visuals.

     
    Dif% Days Method Matrix Test =
    VAR SelectedWeek = MAX('DateTable'[Calendar WeekNumber])
    VAR SelectedYear = MAX('DateTable'[Calendar YearNumber])
    VAR CurrVal = CALCULATE([Total Days],ALL('DateTable'),'DateTable'[Calendar WeekNumber] = SelectedWeek,'DateTable'[Calendar YearNumber] = SelectedYear)
    VAR PrevVal = CALCULATE([Total Days],ALL('DateTable'),'DateTable'[Calendar WeekNumber] = SelectedWeek,
    'DateTable'[Calendar YearNumber] = SelectedYear - 1)
    RETURN
    IF(ISBLANK(CurrVal) || ISBLANK(PrevVal),BLANK(),DIVIDE(CurrVal - PrevVal, PrevVal))
     
     

     

    We noticed some inconsistencies in your data like for example, some weeks have more days, while some others have less days, please check if it is relevant.

     

    Please check the attached .pbix file for your reference and share your thoughts.

     

    Thank you.

5 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    1 year ago

    The only halfway acceptable approach to compare weeks across years is to subtract 364 from the dates in your "current"  week context.

    • dustdaniel's avatar
      dustdaniel
      Icon for Helper II rankHelper II
      1 year ago

      Hi iben, could you guide me on how to do it? 

       

      Or having 2 different matrix one with week comparison and the other with year comparison is the best approach?

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        1 year ago

        The sample data you provided is not clear to me. It also has a lot of #ERROR values.  Could you maybe provide a simpler version that only focuses on what you are trying to achieve?

  • v-hashadapu's avatar
    v-hashadapu
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi dustdaniel , Thank you for reaching out to the Microsoft Community Forum.

     

    Based on your scenario, we created a DAX measure and worked out an answer getting the same output in both card and Matrix visuals.

     
    Dif% Days Method Matrix Test =
    VAR SelectedWeek = MAX('DateTable'[Calendar WeekNumber])
    VAR SelectedYear = MAX('DateTable'[Calendar YearNumber])
    VAR CurrVal = CALCULATE([Total Days],ALL('DateTable'),'DateTable'[Calendar WeekNumber] = SelectedWeek,'DateTable'[Calendar YearNumber] = SelectedYear)
    VAR PrevVal = CALCULATE([Total Days],ALL('DateTable'),'DateTable'[Calendar WeekNumber] = SelectedWeek,
    'DateTable'[Calendar YearNumber] = SelectedYear - 1)
    RETURN
    IF(ISBLANK(CurrVal) || ISBLANK(PrevVal),BLANK(),DIVIDE(CurrVal - PrevVal, PrevVal))
     
     

     

    We noticed some inconsistencies in your data like for example, some weeks have more days, while some others have less days, please check if it is relevant.

     

    Please check the attached .pbix file for your reference and share your thoughts.

     

    Thank you.