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 ...
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

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Thank you so much v-jiascu-msft! I've been banging my head on this for a week. Just some quick notes for future people

         

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

        The above column add will default to Sunday as the week start date. To change to Monday as the start date, simple put ( [Date},2 )

         

        For me, I needed it attached to my main query slicer. The solution I found was adding a column under my main table

         

        Week1 = WEEKNUM('Append1'[Date],2)

         

        I then changed the WoW formula accordingly to reference the main table vs the Calendar table.