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   ...
Need Help
Show and Tell
Tutorial Requests
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).

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

      An idea I'm throwing around now is the moving average type feeling.

       

      So calculate two measures, one a sum of the past seven days, another a sum of the past 14 days.

       

      Then use a countrows function and divide the rows to recieve a daily average.

       

      Then multiply the values by seven.

       

      Seems convoluted, I'm really hoping someone has a better idea.

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

        Or alternativly we get around not using the Week Index number option by sliding it back a week so its always comparing full week index numbers. Was I wrong with my earlier assumption? How would i slide it back a week anyway...