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

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...

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

        I'm feeling the best option is to somehow group the periods I want to consider (such as 7 days, 14 days, 30 days rolling) and then take the average of their sum's.

         

        Thoughts?