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,

 

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

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*

     

  • Jon_vB's avatar
    Jon_vB
    Advocate II
    2 years ago

    I happened to have a "Week offset" number instead of a week #, and so the solution from v-jiascu-msft worked well for me.  The offset is -1 for last week, -2 for the week before, etc.  it's nice for filtering to an 8 week running report, etc.  And - it doesn't have an issue crossing years in this scenario.

     

    On my 'Date' table - the code is just: 

    "Week Offset", DATEDIFF(_today+1,[Date]+1, WEEK),
    (the + 1 is because our weeks end on Friday - so - that's just shifting the calendar forward)

     

    so - my measure looks like:

     

     

     

    WoW Operationally Eligible =
VAR lastWeekAmount =
    CALCULATE (
        SUM ( FactScreening[OperationallyEligible] ),
        FILTER (
            ALL ( 'Date' ),
            'Date'[Week Offset] = MIN ( 'Date'[Week Offset] ) - 1
        )
    )
RETURN
    DIVIDE ( SUM( FactScreening[OperationallyEligible] )- lastWeekAmount, lastWeekAmount, 0 )