Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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?
Solved! Go to Solution.
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
Best Regards,
Jay
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.
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
Best Regards,
Jay
jayw - This worked perfectly, thank you!
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.
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
23 | |
10 | |
10 | |
9 | |
7 |