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 🙂
I think I am going to throw my laptop into the air like a disc and shoot it!!
Here are the components. each in a table. A,B & C Individually are all correct but "D"
Table A is just the COUNT formula from my previous post looking for "injury". This is all correct
Table B is a fixed amount that never changes. So again correct
Table C is ... (Table A x 1,000,000) / Table B. So if I do a quick manual check for Jul 19... (3 x 1,000,000) / 96,000 = 31.25
So with rounding Table C July 19 = 31.25 which is correct
Now Table D is the AVERAGEX formula using TABLE C as the data.. What the points SHOULD be is.
Jul 19 - (0+0+0+0+0+0+0+0+0+0+0+31.25) / 12 = 2.6
Aug 19 - (Zero's + 31 + 10) / 12 = 3.4
Sept 19 - (Zero's + 31 + 10 + 42) / 12 = 6.92
Etc...
Apr 20 ( 0 + 0 + 31 + 10 + 42 + 10 + 10 + 31 + 10 + 21) / 12 = 13.75
So not only are the numbers incorrect but there is some odd behaviour that for 7 months its reading a constant figure??
Anonymous
I think your slicer is making the problem. Because you are applying a slicer to filter the date. At the same time, you are looking for the last 12-month average. So you have to apply all() function to remove this filter. Then only you will get the previous 12 months in your context.
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂
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 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