Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Week over Week Change

 

 

I have been trying to figure out how to get week over week change to display on a table or matrix.  Below are pictures of what the matrix looks like and a sample of the data used. I have calculated a total score by week for each customer and now I just want the difference from week to week.  So, for the top customer the change from Week Ending August 18th to Week Ending August 25th would be -10.6%.  I would also like to be able to calculate the change from the first week on the matrix to the most recent week.  

A couple of important details:

- As you can see in the data, there is a field called "Transaction #".  Each transaction has a total score.  The total score for each customer is going to be the average of all the total scores for individual transactions.  

- In reality, I don't connect to Excel.  I connect live to our backend database.  So, I will have data on my tables that is up-to-the-minute.  In this report, however, I am displaying the previous four weeks scores, starting with the week prior to the current week.  To calculate week over week change, I will be using last week's score and the score from two week's ago.  

Hopefully this all makes sense.  If not, please reply and I'll provide more information.  Thanks in advance. 

 

 

Need Help

3 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

       

      Thank you, v-yulgu-msft.  I saw those before posting my question but was unable to translate them to my data.  I may be able to use the formula below, however I am not trying to find percentage change.  First, I would probably need someone to break down each part of the formula so I understand what it is doing.  Second, the final part of the formula seems to be figuring the % change over the previous week.  How would that part change to give me just week over week change in numbers, not percentage change.  

       

      Measure =
VAR lastWeekAmount =
    CALCULATE (
        SUM ( Append1[Video Views] ),
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[WeekNum]
                = MIN ( 'Calendar'[WeekNum] ) - 1
        )
    )
RETURN
    DIVIDE ( SUM ( Append1[Video Views] ) - lastWeekAmount, lastWeekAmount, 0 )
      • v-yulgu-msft's avatar
        v-yulgu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        Hi Anonymous,

         



         How would that part change to give me just week over week change in numbers, not percentage change.  

        Measure =
VAR lastWeekAmount =
    CALCULATE (
        SUM ( Append1[Video Views] ),
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[WeekNum]
                = MIN ( 'Calendar'[WeekNum] ) - 1
        )
    )
RETURN
   SUM ( Append1[Video Views] ) - lastWeekAmount

        Regards,

        Yuliana Gu