Forum Discussion
DAX for Calculating Moving Average
Hello Experts,
I think this is an easy question and I've just overcomplicated it. I have two measures and need one output.
- Sum of La2O3 g/L
- Moving Average of La2O3 g/L at different durations (ex: 30 Day MA, 60 Day MA, etc.)
Output = (Correct) Variance between 1 & 2.
Code Used for Moving Average:
MA 30 (La2O3) =
//Selecting the date in the range
VAR LastestDate =
MAX ( 'Date'[Date] )
//Defining the duration to be considered for average calculation(k)
VAR Dura = 30
//Filtering the Calendar Table for the defined range
VAR CalculationPeriod =
FILTER (
ALL ( 'Date' ), -- Removing any external filters context applied --
AND (
'Date'[Date] > LastestDate - Dura, -- the range start date --
'Date'[Date] <= LastestDate -- the range end date --
)
)
//Calculating the Moving Average
VAR MovingAverage =
IF (
COUNTROWS ( CalculationPeriod ) >= Dura, -- Condition to check minimum number of days for the calculation --
CALCULATE (
AVERAGEX('SXDTable',[^La2O3(g/L)]), -- Calculating average of total amount --
CalculationPeriod
)
)
RETURN
MovingAverage
Same Code, nicer display.
When I look at my two outputs individually I get the correct values. Let's use C101 as an example.
- 30 Days = Easy two values on Dec. 5th and 7th, 42.84 and 43.89 respectively. This averages to ~43.37. Check!
- 60 Days = Four values. 11/13. 11/29. 12/5. 12/7. Averages to 30.32. Check!
- 90 Days = Eight values. 10/3 - Current. Averages to 26.79. Check!
Now, what'd I like to do is evaluate the Moving Average vs. Individual Dates. I thought I'd just be able to subtract the two. However, each return is 0, regardless if I do it via Calculated Column or Measure minus Measure.
Calculated Column Output =
Measure Output =
This is the 30 day MA so the last two entries evaluate against themselves and give an output of 0 and the rest evaluate against 0 and return themselves.
Instead what I would like is for the first two entries to evaluate against the 30 day moving average. In this case, we'd have 43.89 (output) vs. 43.37 (MA) and 42.84 (output) vs. 43.37 (MA). There's some rounding going on, but essentially since n=2, I'd expect an output of +0.53 and -0.53 for Output - MA.
So, I just re-read this, and, ultimately, it appears I need help.... subtracting....? Hopefully, someone over a 1st grade math level can help me 🙃
Thanks in advance and even just for the time to read it! I really appreciate it.
Hi djkoenig
For the questions you raised, here are the solutions I offered:
I used the data you provided
“data”
“Date”
Create a measure to find La2O3(g/L) within 30 days
MA 30(La2O3) = var max_date = MAX('Date'[Date]) var _la2o3 = CALCULATE(max('data'[La2O3 (g/L)]), FILTER(ALL('data'), [Cell] = MAX('data'[Cell]) && 'data'[La2O3 (g/L)] = MAX('data'[La2O3 (g/L)]) && [Date] > EDATE(max_date, -1) )) RETURN _la2o3Calculate the average of MA 30 (La2O3).
MA 30 (La2O3)_avg = var max_date = MAX('Date'[Date]) var _la2o3_avg = CALCULATE(AVERAGE('data'[La2O3 (g/L)]), FILTER(ALL('data'), [Cell] = MAX('data'[Cell]) && [Date] > EDATE(max_date, -1) )) RETURN _la2o3_avgIf you want to remove blank rows from MA 30 (La2O3), you can do the following: you can apply "is not blank" to MA 30 (La2O3) in the Filters
Here is the result:
result = var max_date = MAX('Date'[Date]) var _la2o3 = CALCULATE(max('data'[La2O3 (g/L)]), FILTER(ALL('data'), [Cell] = MAX('data'[Cell]) && 'data'[La2O3 (g/L)] = MAX('data'[La2O3 (g/L)]) && [Date] > EDATE(max_date, -1) )) var _la2o3_avg = CALCULATE(AVERAGE('data'[La2O3 (g/L)]), FILTER(ALL('data'), [Cell] = MAX('data'[Cell]) && [Date] > EDATE(max_date, -1) )) RETURN _la2o3 - _la2o3_avgBest Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
Hi djkoenig
For the questions you raised, here are the solutions I offered:
I used the data you provided
“data”
“Date”
Create a measure to find La2O3(g/L) within 30 days
MA 30(La2O3) = var max_date = MAX('Date'[Date]) var _la2o3 = CALCULATE(max('data'[La2O3 (g/L)]), FILTER(ALL('data'), [Cell] = MAX('data'[Cell]) && 'data'[La2O3 (g/L)] = MAX('data'[La2O3 (g/L)]) && [Date] > EDATE(max_date, -1) )) RETURN _la2o3Calculate the average of MA 30 (La2O3).
MA 30 (La2O3)_avg = var max_date = MAX('Date'[Date]) var _la2o3_avg = CALCULATE(AVERAGE('data'[La2O3 (g/L)]), FILTER(ALL('data'), [Cell] = MAX('data'[Cell]) && [Date] > EDATE(max_date, -1) )) RETURN _la2o3_avgIf you want to remove blank rows from MA 30 (La2O3), you can do the following: you can apply "is not blank" to MA 30 (La2O3) in the Filters
Here is the result:
result = var max_date = MAX('Date'[Date]) var _la2o3 = CALCULATE(max('data'[La2O3 (g/L)]), FILTER(ALL('data'), [Cell] = MAX('data'[Cell]) && 'data'[La2O3 (g/L)] = MAX('data'[La2O3 (g/L)]) && [Date] > EDATE(max_date, -1) )) var _la2o3_avg = CALCULATE(AVERAGE('data'[La2O3 (g/L)]), FILTER(ALL('data'), [Cell] = MAX('data'[Cell]) && [Date] > EDATE(max_date, -1) )) RETURN _la2o3 - _la2o3_avgBest Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I really appreciate the effort on this! That does in fact work. I can now do some additional analysis.
I liked your edate (max date - 1) to gate time as well. Answer accepted!
It is difficult to understand your question with just text and images. Share the download link of the PBI file. Clearly show the problem there and the expected result.
Ah apologies Ashish_Mathur. I can get a bit wordy. Had to create a sample dataset, but here is the PBIX:
https://drive.google.com/file/d/1TNqbJIIH9PVoxMDrtUy2DEjZMbGKRjsi/view?usp=drivesdk [drive.google.com]
I'd like for the aggregated value to append to each individual line, even with the presence of date. This way I can subtract an individual value from the moving average and decide whether variance is material.From the sample PBIX, instead of 42.84 and 43.89 for the MA 30 (La2O3) value, I'd like to see the average 43.37 for both instances.
Does that help clarify? Also, pretty new, so I'm probably doing something goofy.
Access Denied message.
