Forum Discussion
Post ProdigyCompare Last Week's Sum and Past Two Week's Sum Average
Post Prodigy
= 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).
Post ProdigyAn 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
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
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?
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