Forum Discussion
Post ProdigyCompare Last Week's Sum and Past Two Week's Sum Average
Post ProdigyOr 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...
Post ProdigyI'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?
I have done some work on this.
I have assumed you have a table of week numbers along with Sales in each week.
1. Created a table as follows:
WeekNum Sales 1 189 2 266 3 174 4 240 5 378 6 300 7 258 8 490 9 539 10 81 2. Created a measure
RunTot:=CALCULATE (
SUM ( 'Table2'[Sales]),
FILTER ( ALL ( Table2 ), Table2[WeekNum] >= MAX ( 'Table2'[WeekNum] ) - 2 &&Table2[WeekNum] <= MAX ( 'Table2'[WeekNum] ) -1 ))
This creates the total for the previous two weeks prior to the current week row.
If you want to compute for current week and past week change the measure as
RunTot:=CALCULATE (
SUM ( 'Table2'[Sales]),
FILTER ( ALL ( Table2 ), Table2[WeekNum] >= MAX ( 'Table2'[WeekNum] ) - 1 &&Table2[WeekNum] <= MAX ( 'Table2'[WeekNum] ) ))
3. Created a measure to find the number of past data rows available in the data against each week
TotRows:=CALCULATE (
COUNTROWS( 'Table2'),
FILTER ( ALL ( Table2 ), Table2[WeekNum] >= MAX ( 'Table2'[WeekNum] ) - 2 &&Table2[WeekNum] <= MAX ( 'Table2'[WeekNum] ) - 1))
This is similar to the RunTot Measure except that instead of Sum([Sales]) it counts the rows satisfying the week condition.
4. Now for the Average of the past weeks
created a measure
AverageSales:=Divide([RunTot],[TotRows])
5. The final out put is as follows :
Row Labels Sum of Sales RunTot TotRows AverageSales 1 189 2 266 189 1 189 3 174 455 2 227.5 4 240 440 2 220 5 378 414 2 207 6 300 618 2 309 7 258 678 2 339 8 490 558 2 279 9 539 748 2 374 10 81 1029 2 514.5 Grand Total 2915 1029 2 514.5 6. There are few challenges you have. How are you numbering the Week Numbers ? What happens when a new year starts how do you nuber the weeks ?
Hope this meets your requirement.
If so please accept this as solution and also give Kudos.
Cheers
CheenuSing
- ElliotP
Anonymous I think that's very very good progress. It doesn't do what I'd like, but its definitly got the code to build from.
I'd like to for example, say I have 14 days of data. 14 amounts of sales, one per day.
I would like to find the sum of sales for each week. We can do this through using a week Number and summing the sales (would appreciate the code for this if you're so inclined), this doesn't achieve what I'd like to do in the rolling sense, but we can work on that another time.
Then be able to create a measure which then averages different sums of weeks. So One measure I'd like would be to sum average the past two weeks; one would be the past 6 weeks.
The two ideas I'm working from atm are
1. Downgrade what I'd like and just do averages using the datesinperiod filter.
2. Play with PowerQuery and row filtering to create some tables with filtered setups (admittedly not a fan as this will be very very very labourous).
- ElliotP
Current thinking;
1. Take the sum of the past two weeks using the datesinperiod function
2. Since I want to find the Sum of each week, for the past two weeks, divide the Sum measure by 2. This produces a rolling average weekly sum over the period I want to consider.
3. Average this function over the past 14 days using the datesinperiod function to find a rolling average of my average in 2. which gives me the average weekly sum per week for the period under consideration.
It's quite late at night, so I'm not sure if I'm making any sense or whether I've just found averages on averages.