Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Week over Week Change

Hey All,

 

I am trying to figure out week over week change for metrics by social platforms (i.e comments on twitter, comments on facebook etc.)  What I can't wrap my head around is each post has a different day and metric associated and how I would roll that up. I included the time frame colums I have. Thanks everyone!

Need Help
Tips and Tricks
Tutorial Requests
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi Anonymous,

     

    Try this measure please. 

     

    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 )

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    I have upgraded the file here: https://1drv.ms/u/s!ArTqPk2pu-BkgROntwcC_mRlVBAU

     

    Best Regards!

    Dale

8 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi Anonymous,

     

    I think you have done the most of the work. You have week numbers. You can sum up or average base on week number. 

    I would suggest creating a new date table in case you would use some time intelligence functions.

    1. Create a date table.

    Calendar =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2016, 1, 1 ), DATE ( 2017, 12, 31 ) ),
    "WeekNum", WEEKNUM ( [Date] )
)

    2. Add a new column to "Append1" due to the dates have time part.

    DateForRelationship = [Date].[date]

    3. Establish relationship.

    Please check this file: https://1drv.ms/u/s!ArTqPk2pu-BkgROntwcC_mRlVBAU. There is a demo of visual in it.

     

    Best Regards!

    Dale

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      v-jiascu-msftAwesome, thanks so much! any idea how I would go about now changing this into a week over week percent change like you mentioned?

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        8 years ago

        Hi Anonymous,

         

        Try this measure please. 

         

        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 )

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

        I have upgraded the file here: https://1drv.ms/u/s!ArTqPk2pu-BkgROntwcC_mRlVBAU

         

        Best Regards!

        Dale