Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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
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
@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] )
@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 🙂