DAX for Calculating Moving Average

[djkoenig]

Hello Experts, 

I think this is an easy question and I've just overcomplicated it. I have two measures and need one output. 

1. Sum of La2O3 g/L
2. 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. 

[v-nuoc-msft · Accepted solution]

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 _la2o3 

Calculate 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_avg

If 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_avg

Best Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[djkoenig]

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!

[Ashish_Mathur]

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.

[djkoenig]

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.co...

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.

[Ashish_Mathur]

Access Denied message.

[djkoenig]

@Ashish_Mathur I thought I had specified permissions. My mistake. You should be good now.

https://drive.google.com/file/d/1TNqbJIIH9PVoxMDrtUy2DEjZMbGKRjsi/view?usp=drive_link

If for some reason you still can't access, it's a whole 8 lines of data for the test. 

Cell	Cell Path	Date	La2O3 (g/L)
C101	Extraction	12/7/2023	43.89
C101	Extraction	12/5/2023	42.84
C101	Extraction	11/29/2023	25.27
C101	Extraction	11/13/2023	9.27
C101	Extraction	10/15/2023	9.75
C101	Extraction	10/8/2023	14.6
C101	Extraction	10/3/2023	41.88

[Ashish_Mathur]

I cannot understand what you want.  In another tab of the PBI file, show the expected result very clearly.