Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

7 day moving average

I really have zero DAX experience.  I even tried to add a column and then enter an Excel formula at first.  Of course after digging in a little I realized that it doesn't work that way in this case. ...
Analysis
Business
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    Please check the measure:

    Measure = CALCULATE(SUM('Table'[qtyordered]),FILTER(ALLSELECTED('Table'),'Table'[orderdate]>SELECTEDVALUE('Table'[orderdate])-7&&'Table'[orderdate]<=SELECTEDVALUE('Table'[orderdate])))/7

     

    Best Regards,

    Jay

truptis's avatar
truptis
Icon for Community Champion rankCommunity Champion
4 years ago

Hi Anonymous , 

Try using this:

TotalSales = Sum(Sales)

 

Avg Sales (MA 7 days) =
//Selecting the date in the range
VAR _LastDate =
MAX(Calendar[Date] )

VAR _Duration = 7  //Defining the duration to be considered for average calculation(k)
//For filtering the Calendar Table for the defined range
VAR_CalculationPeriod =
FILTER(
ALL(Calendar ),
AND(Calendar[Date] > _LastDate — _Duration, — the range start date
Calendar[Date] <= _LastDate — the range end date
)
)
VAR _MovingAverage =
IF (COUNTROWS ( _CalculationPeriod ) >= _Duration, 
CALCULATE(AVERAGEX(Calendar,[TotalSales]), _CalculationPeriod))
RETURN
_MovingAverage

 

Anonymous -> Please hit the thumbs up & mark it as a solution if it works for you. Thank you.