Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
dmunger
Regular Visitor

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.  I am using direct query from a SQL database.

 

My query data looks like the below table - and I am trying to add the final 7 day moving average column.  In short, I need to sum the quantity ordered on each date and then calculate the 7 day moving average on those sums.  I would then add that data to the line graph below.

 

What is the best way to go about doing this?  In addiiton, is there a particular trainng course I can take to come up to speed? 

dmunger_0-1647275416513.png

dmunger_1-1647275681243.png

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @dmunger ,

 

Please check the measure:

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

1.jpg

 

Best Regards,

Jay

View solution in original post

4 REPLIES 4
dmunger
Regular Visitor

truptis - thank you for this.  I couldn't figure out how to modify it for my report, but that's all on me.  The measure that was suggested seems to be working great though, so I have a working model now.  Again, thank you.

Anonymous
Not applicable

Hi @dmunger ,

 

Please check the measure:

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

1.jpg

 

Best Regards,

Jay

jayw - This worked perfectly, thank you!

truptis
Community Champion
Community Champion

Hi @dmunger , 

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

 

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

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.