Forum Discussion
Solved
Averageif
Hi, I try to do 3 weeks MA and 'Ave per week' for the below data. Note 'Ave per week' is for info only. Week NScheduled Ave per week 1 1 1 1 1 1 1 1 1 1 1 1 2 6 3.5...
Hi tomgag
In your original table, create a calculated column
Ave per week =
DIVIDE (
CALCULATE ( SUM ( Table1[NScheduled] ), ALLEXCEPT ( Table1, Table1[Week] ) ),
CALCULATE ( COUNT ( Table1[Week] ), ALLEXCEPT ( Table1, Table1[Week] ) )
)Then create a new table from Table1
Table2 = SUMMARIZE(ALL(Table1),[Week],Table1[Ave per week])
In this table, create calculated columns
moving sum = CALCULATE ( SUM ( 'Table2'[Ave per week] ), FILTER ( ALL ( 'Table2' ), [Week] < EARLIER ( 'Table2'[Week] ) && [Week] >= EARLIER ( 'Table2'[Week] ) - 3 ) )
MA3 = [moving sum]/3Best Regards
maggie
Hi,
What does your actual base data look like? Is there is a Date column in there? If no, do you atleast have a year and Month column? Along with the Date column (if it is there), will there be a week column as well?
Share the actual data and show the expected result.