Forum Discussion

ElliotP's avatar
ElliotP
Icon for Post Prodigy rankPost Prodigy
10 years ago

Compare Last Week's Sum and Past Two Week's Sum Average

Afternoon,

 

I'm trying to build a guage to compare the sum of last week's sessions with the average sum of the past two weeks.

 

For Example:

Week One: 10 Page Hits

Week Two: 20 Page Hits

 

So; Last Week's Sum of Page hit's is 10.

And the Last Two Week's Sum's Average by Week is (10+20/2) = 15

 

I've given it a try using dateadd, datesinperiod, datesbetween and fiddling around with some EARLIER or Date[DateKey]-28 for example; but since I need it on a rolling basis; we can't simply choose between a specific start and end date.

 

If I try to do:

Past Two Weeks Sum Average = Calculate(Sum(NumberofSessions), DATESINPERIOD (NumberofSessionsDateTable, TODAY(), -14, DAY))

It returns me of course the sum of the past two weeks.

 

I've thought of maybe breaking it up and using measures and some addition and subtraction to make it work; but I get stuck with the same issue of how for example to choose between -7 and -14 day interval on a rolling basis.

 

I'd prefer not to have to dynamically filter a bunch of tables to do this as I want to compare over a few different periods (enough so that it would take ages).

 

Any thoughts would be greatly appreciated.

Need Help
Show and Tell
Tutorial Requests

16 Replies

  • ElliotP's avatar
    ElliotP
    Icon for Post Prodigy rankPost Prodigy
    10 years ago

    Link to source

     

    Last 2 Weeks = 
CALCULATE (
[Sumsessions13],
FILTER (
ALL ( 'SessionsWkIndex' ),
'SessionsWkIndex'[Week Index] >= MAX ( 'SessionsWkIndex'[Week Index] ) -2
&& 'SessionsWkIndex'[Week Index] <= MAX ( 'SessionsWkIndex'[Week Index]  ) 
)
)

    We can adapt this for Dayindex.

     

    I'm just wondering if someone could walk me through using this.

     

    Would I do this for days and simply add a -7 and a -14 in the place of the -2 and after the second bracket after <=MAX. And then Calc the averages this way?

    • ElliotP's avatar
      ElliotP
      Icon for Post Prodigy rankPost Prodigy
      10 years ago

      Doing:

       

      Last 2 Weeks = 
CALCULATE (
[Sumsessions13],
FILTER (
ALL ( 'SessionsWkIndex' ),
'SessionsWkIndex'[Week Index] >= MAX ( 'SessionsWkIndex'[Week Index] ) -7
&& 'SessionsWkIndex'[Week Index] <= MAX ( 'SessionsWkIndex'[Week Index]  ) -14 
)
)

      Returns a blank. Hmmm

      • ElliotP's avatar
        ElliotP
        Icon for Post Prodigy rankPost Prodigy
        10 years ago

        http://www.powerpivotpro.com/2011/03/advanced-dax-calculation-doing-a-moving-grouped-average-in-powerpivot/

         

        =
	CALCULATE(
		 AVERAGEX(VALUES(Sales[Week No]), Sales[Sum of Sales])
		 ,Sales[Week No] <= VALUES(Sales[Week No]) &&  Sales[Week No] > VALUES(Sales[Week No])-2
		)
, )

        I'm not sure if I take out the if function if it will make it dynamic.

         

        Then the concern is when i convert to days. Would I need to create a rolling group week?

         

        I feel I'm missing something here quite basic.

         

        Side Note; If I stay in weeks, when the dashboard updates daily won't it simply click over to the next week so for 6 days a week the data will be massivly skewed when comparing past 7 days vs past 14 days (as two groups of 7 day weeks).