Trying to get Average of Change In a Value From One Month to the Next

[Anonymous]

I have the below matrix of invoices on hold for 2 months of data so far.

I have calcualated the "Delta" as the change from previous month.  There is no data for July so the result of the calculation for August is the same as the number of invoices.

In the total column, somehow my Dax is counting the nubmer of invoices instead of the number of invoices last month.  For exmple the first row of the matrix the total column for Delta under the heading "Avg Number" reads "10947" which is the sum of the number of invoices for Aug and Sept(4125+6822).

The Dax is below.  Why is this happening and how do I get my Dax to count the number of invoices last month for each month, in the total column?

Mark Janecek

PTP Metrics Analyst 
Johsn

[MFelix · Accepted solution]

Hi @Anonymous,

Measures are calculated based on context so in this case when you go to the total value of your measure you are not making the average but in fact you are making the calculation of the total invoices - previous month total invoices are the two months and previous is 0 becuase there are no values.

In this case you need to add a AVERAGEX in order to achieve what you need should be something similar to:

AVERAGE = IF (HASONEVALUE(Table[Monthyear]), [Delta], AVERAGEX(VALUES(Table[Monthyear]), [Delta]))

Be aware that depending on your model this may need some adjustments

[Anonymous]

Thank You Miguel,  That worked great!

[MFelix]

Hi @Anonymous 

Glad I could help don't forget to mark the correct answer so it can help other users.