Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello,
I have an issue with a rolling average I created with DAX in a line chart. The first 4 days are deflated because it's still thinking I want a 5 day average for the first 1, 2, 3, and 4 days...
Here is the DAX:
Solved! Go to Solution.
Hi @jconte12 ,
I created some data:
Here are the steps you can follow:
1. Create calculated column.
rank = RANKX(ALL('Table'),'Table'[date],,ASC)
mod =
var _mod=MOD('Table'[rank],5)
return
IF(
_mod=1,1,0)
Group =
CALCULATE(SUM('Table'[mod]),FILTER(ALL('Table'),'Table'[date]<=EARLIER('Table'[date])))
2. Create measure.
Measure =
var _1=MAX('Table'[Date])
return
AVERAGEX(
FILTER(
SUMMARIZE(ALL('Table'),
'Table'[Date],'Table'[Group],
"Avg value",AVERAGE('Table'[amount])),
'Table'[Date]<=_1&&'Table'[Group]=MAX('Table'[Group])),
[Avg value])
3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @jconte12 ,
I created some data:
Here are the steps you can follow:
1. Create calculated column.
rank = RANKX(ALL('Table'),'Table'[date],,ASC)
mod =
var _mod=MOD('Table'[rank],5)
return
IF(
_mod=1,1,0)
Group =
CALCULATE(SUM('Table'[mod]),FILTER(ALL('Table'),'Table'[date]<=EARLIER('Table'[date])))
2. Create measure.
Measure =
var _1=MAX('Table'[Date])
return
AVERAGEX(
FILTER(
SUMMARIZE(ALL('Table'),
'Table'[Date],'Table'[Group],
"Avg value",AVERAGE('Table'[amount])),
'Table'[Date]<=_1&&'Table'[Group]=MAX('Table'[Group])),
[Avg value])
3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @jconte12 , Here's another example for moving average if that might help?
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
84 | |
84 | |
65 | |
62 | |
55 |
User | Count |
---|---|
171 | |
110 | |
109 | |
73 | |
71 |