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. 

    • lamateo's avatar
      lamateo
      Frequent Visitor
      6 years ago

      I used this but the calculation is not showing the right numbers. I have my Fiscal calendar all set with the correct fiscal week number. My guess it has something to do with week numbers the same between multiple fiscal years. Anybody encountered this and have a fix?

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        I was trying to find a solution to my week-over-week as well and this works until I cross over a year boundary and then it breaks. It's like there isn't a way for it to handle doing a comparison between the week of the year. Maybe, when building the calendar table we need to have a new column which is the number year * 52 + the week of the year...which won't work when we have a year with 53 weeks......*shrug*