Forum Discussion
AVERAGEX Problems
Hello all, I am pulling my hair out with an AVERAGEX formula Water TRIFR 12MMA = AVERAGEX( DATESINPERIOD ( 'Calendar'[Date], LASTDATE ( 'Calendar'[Date] ), -12, MONTH ), [Water TRIFR] ...
Anonymous
Try this
Water TRIFR 12MMA = VAR monthrange = 12 VAR result = DIVIDE ( SUMX ( DATESINPERIOD ( 'Calendar'[Date], LASTDATE ( 'Calendar'[Date] ), -1 * monthrange, MONTH ), [Water TRIFR] ), monthrange, BLANK () ) RETURN result
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂
If I reduce the data and remove all of the non-essential stuff to this problem it would only be
| Incident # | Date Reported | Report Type |
| 1 | 1/7/19 | Injury |
| 2 | 1/8/19 | Non-Injury |
| 3 | 20/8/19 | Injury |
| 4 | 14/9/19 | Injury |
| 5 | 4/1/20 | Non-Injury |
| 6 | 18/2/20 | Non-Injury |
| 7 | 24/3/20 | Non-Injury |
| 8 | 24/3/20 | Injury |
| 9 | 2/4/20 | Non-Injury |
This is example only. I didnt get exact dates from actual data
Anonymous
Try this
Water TRIFR 12MMA =
VAR monthrange = 12
VAR result =
DIVIDE (
SUMX (
DATESINPERIOD (
'Calendar'[Date],
LASTDATE ( 'Calendar'[Date] ),
-1 * monthrange,
MONTH
),
[Water TRIFR]
),
monthrange,
BLANK ()
)
RETURN
result
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂