Average X not calculating as expected:

[ShawnnaBee]

Hiya All!

I am new to DAX outside of simple commands. I have been working on a project, and need to have a moving average of a difference between two measures. I attemted to use AVERAGEX, but the result was not the correct calculation. So I wrote it out with a ton of variables, and a switch to indicate direction of the trend.

Can someone help me condense this formula? I am having to create 33 measures that do the same calculation on 66 measures, and my load is getting quite slow. It works perfect, just looking to speed it up:

KPI Rev Trend =
VAR Month1 = Calculate(SUM('KPI Measures'[Revenue]), LASTDATE('KPI Measures'[Period Beginning]))
VAR Month2 = CALCULATE([KPI  Rev], DATEADD(LASTDATE('KPI Measures'[Period Beginning]), -1, MONTH))
VAR Month3 = CALCULATE([KPI Rev], DATEADD(LASTDATE('KPI Measures'[Period Beginning]), -2, MONTH))
VAR Month4 = CALCULATE([KPI Rev], DATEADD(LASTDATE('KPI Measures'[Period Beginning]), -3, MONTH))
VAR Month5 = CALCULATE([KPI Rev], DATEADD(LASTDATE('KPI Measures'[Period Beginning]), -4, MONTH))
VAR Month6 = CALCULATE([KPI Rev], DATEADD(LASTDATE('KPI Measures'[Period Beginning]), -5, MONTH))
VAR Month7 = CALCULATE([KPI Rev], DATEADD(LASTDATE('KPI Measures'[Period Beginning]), -6, MONTH))
VAR Mo1Diff = Month1-Month2
VAR Mo2Diff = Month2-Month3
VAR Mo3Diff = Month3-Month4
VAR Mo4Diff = Month4-Month5
VAR Mo5Diff = Month5-Month6
VAR Mo6Diff = Month6-Month7
VAR TotalDiff = Mo1Diff+Mo2Diff+Mo3Diff+Mo4Diff+Mo5Diff+Mo6Diff
VAR ChartIncrease = UNICHAR(128200)
VAR ChartDecrease = UNICHAR(128201)
VAR SixMonthTrend = TotalDiff/6
RETURN

SWITCH(TRUE(),
SixMonthTrend>= 0, ChartIncrease,
SixMonthTrend<=0,ChartDecrease
)

Thank you so much for your help 🙂

S

[v-yetao1-msft]

Hi @ShawnnaBee 

Can you provide your original data (delete private data)? At present, there is no problem in the formula you provided.

Best Regard

Community Support Team _ Ailsa Tao

[ShawnnaBee]

Good Morning @v-yetao1-msft 
The Data is provided below. AverageX is only reporting the M1 and M2 subtracted and divided by 1. This is not an average of the entire set. What I am looking for is a rolling average of MoM difference for last 6 months of data.

Thank you!
S

[amitchandak]

@ShawnnaBee , Try measure like, change m3 as per need

M1= Calculate(SUM('KPI Measures'[Revenue]), LASTDATE('Date'[Date]))
M2 = CALCULATE([KPI Rev], DATEADD(LASTDATE('Date'[Date], -1, MONTH))

diff = [M1] -[M2]
m3 = AverageX(values('Date'[Month]),[diff] )

[ShawnnaBee]

@amitchandak Thank you so much for the quick response!!

Sadly, I am still getting the same result which is only the diference between the first months So AverageX shows me the difference between M1 and M2, instead of the difference over the range of months:

Data for ease:

Month	Revenue
M14	$                            26,658,215
M13	$                            63,551,487
M12	$                            32,565,346
M11	$                            15,698,478
M10	$                            32,785,412
M9	$                            82,563,143
M8	$                            28,256,314
M7	$                            56,982,574
M6	$                            33,259,306
M5	$                            79,684,190
M4	$                            85,254,151
M3	$                            49,368,427
M2	$                          156,989,382
M1	$                            21,254,125

Thank you so much 🙂