AVERAGEX Problems

[Anonymous]

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]

)

This is the formula i am using and I use it in 2 different reports, reporting on the same data but just for different parts of the business.

The one report produces this which is correct and the expected output.

The other report with the exact same formula produces this which is not at all the output and is strange that the first 7 months are identical??

I have also double and triple checked the data that supplies the figures for this and have isolated them and tested that they are working independantly. The pic below the line graph to the right shows me that if I evaluate things on a month by month basis that they are correct... So the graph on the left (and above) should more closely match the peaks and troughs??

Any thoughts?

J

[nandukrishnavs]

@Anonymous 

Create another DAX measure and add that in the tooltip.

LastDateValue=LASTDATE ( 'Calendar'[Date] )

 Then verify its value in both visuals. Maybe it is creating issue.

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

[Anonymous]

@nandukrishnavs 

Hey,

Tried. Yup they are all showing last day of month.

[nandukrishnavs]

@Anonymous 

Try this 

Water TRIFR 12MMA =

AVERAGEX (

    DATESINPERIOD (

        'Calendar'[Date].[Date],

        LASTDATE ( 'Calendar'[Date].[Date] ),

        -12,

        MONTH

    ),

    [Water TRIFR]

)

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂

Here is my latest blog

https://community.powerbi.com/t5/Community-Blog/Dynamic-Page-Navigation-Based-on-User-Login/ba-p/1097786

[Anonymous]

@nandukrishnavs 

Hey,

Nope, that makes the graph go blank

[nandukrishnavs]

@Anonymous  Can you prepare a sample dataset with the same issue and share it here?

[Anonymous]

@nandukrishnavs 

@AllisonKennedy 

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??

[nandukrishnavs]

@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]

@nandukrishnavs 

Hey,

Where do I apply that all()?

I checked the filters and they are (all)

[nandukrishnavs]

@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 🙂

[Anonymous]

@nandukrishnavs 

I would except the data is very sensitive and contains medical and personal contact information 😞

There was also an error with that DAX?

[nandukrishnavs]

@Anonymous  Understood. If you can replicate 20 rows with masked data would be enough.

[Anonymous]

@nandukrishnavs 

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

[nandukrishnavs · Accepted solution]

@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]

@nandukrishnavs 

My hero!

[Anonymous]

@nandukrishnavs 

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?

[AllisonKennedy]

I'm not 100% sure how the graph on the bottom right relates to the AVERAGEX measure in question? 

What is the DAX formula for [Water TRIFR] and what does that Measure look like when plotted against the same time period in the same report and page as the graph on the bottom left? 

[Anonymous]

@AllisonKennedy 

Hey,

So the graph on the bottom right is a DAX formula that looks through a data set and counts the number of entries that have the correct value. Here is the DAX for that

CAMMS Total Injury = CALCULATE(COUNT('CAMMS Incidents'[Report Type]),'CAMMS Incidents'[Report Type] IN {"Injury"})

So I use that to test to see a simple count each month, I then went back to the data and did a manual physical count to see if it matched (which it does)

I then take that amount and put it into an industry specific formula that shows a "frequency rate"

Water TRIFR = ([CAMMS Total Injury]*1000000) / ([Water Hours / Month])

This forumla shows me for every million hours worked how many injuries do we have. 

I then plot that into my 12MMA formula from before.