Forum Discussion
AVERAGEX Problems
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 🙂
Anonymous
Try this measure.
Water TRIFR 12MMA =
AVERAGEX (
DATESINPERIOD (
ALL ( 'Calendar'[Date] ),
LASTDATE ( 'Calendar'[Date] ),
-12,
MONTH
),
[Water TRIFR]
)If you can share the dataset, I can validate it.
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂
I would except the data is very sensitive and contains medical and personal contact information 😞
There was also an error with that DAX?
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 🙂Anonymous Understood. If you can replicate 20 rows with masked data would be enough.
How do I attach an xls file? there is no options?
I created a table and pasted the data but it re-arranged all the cells?
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