cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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!Snap shot one.PNGSnap shot two.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Week over Week Change

Hi @Philsap,

 

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 )

week over week change.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
Highlighted
Helper I
Helper I

Re: Week over Week Change

Here's a Pbix so you can see the data

 

https://we.tl/nJdKC2ez8V

Highlighted
Microsoft
Microsoft

Re: Week over Week Change

Hi @Philsap,

 

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper I
Helper I

Re: Week over Week Change

@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?

Highlighted
Microsoft
Microsoft

Re: Week over Week Change

Hi @Philsap,

 

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 )

week over week change.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Helper I
Helper I

Re: Week over Week Change

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. 

Highlighted
Frequent Visitor

Re: Week over Week Change

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?

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors