Difference from previous row and averaging

[Anonymous]

Hello,

I am trying to convert a report we're currently using based on manual data input to Excel. Instead we'd like to use PowerBI. I have a dataset which combines several Excel files into one dataset in Power BI called Query1, This is for historic check points like the image shows below. I'm trying to figure out- and need help with 2 things:

1. How to calculate the difference in the Subtotal from previous report date.

e.g.

Total	Difference from previous row
6433	87
6351	82
6245	106

Matrix visualization:

Rows = Query1[ReportDate]

Columns and Values = Query1[Country]

2. I would then like to use the values calculated and average them out in a KPI, something like below. This is to get a sense of historical averages based on the difference. Most preferred is to average the past n report dates to make it more dynamic.

[Anonymous]

I managed to get it working using this code from @mahoneypat :

Change = 
VAR thisvalue = COUNTA (Query1[Country] )
VAR thisdate = MAX ( Query1[Report Date] )
VAR prevdate = CALCULATE (
MAX ( Query1[Report Date] );
ALL ( Query1[Report Date] );
Query1[Report Date] < thisdate)
VAR prevvalue = CALCULATE( COUNTA ( Query1[Country] ); Query1[Report Date] = prevdate )
RETURN
IF(prevvalue <> BLANK(); thisvalue - prevvalue;0)

This code gives the correct results on every row, but the total at the bottom is wrong:

This then gives the wrong results when averaging (expected should be -152, or if excluding the top "0" row it should be -157):

Change AVG = AVERAGEX( VALUES(Query1[Report Date]); [Change])

From what I can understand by googling it could be caused by the total is summing up everything in the column and not only the measure values.

When I attempt the last n Averaging code I get the following error message:

Change AVG Last 4 = 
VAR thisdate =
MAX ( Query1[Report Date] )
RETURN
CALCULATE (
[Change AVG];FILTER (
ALL ( Query1[Report Date] );
Query1[Report Date] <= thisdate
&& Query1[Report Date] >= (thisdate - 4)
)
)

MdxScript(Model (39,4) Calculation error in measure 'Query1'[Change AVG Last 4]: DAX Comparison operations do not support comparing values of type Text with values of type Number. Consider using the VALUE or FORMAT function to convert one of the values.

[Anonymous]

Thank you very much for all feedback @Greg_Deckler, @mahoneypat, and @Anonymous . I've attempted to apply suggestions but suspect I left one important part unclear. The values used in the Matrix for Query1[Country] is aggregated based count (not sum), they're individual rows (real data blurred):

This is the result I get in my attempts to apply feedback:

Have I misunderstood anything?

[Anonymous]

HI @Anonymous 

I used the below in one of my report yesterday to get the previous row data. Although I had to get the chage %, but in your case it will be one calculation less.

Calculated Column =
VAR PreviousRow =
    TOPN (
        1,
        FILTER (
            Table1,
            Table1[Date] < EARLIER (Table1[Date])  //  try '>'  if '<' donesn't give you the desires result
        ),
        [Date], DESC
    )
VAR PreviousValue =
    MINX ( PreviousRow, [revenew] )
RETURN
    ([revenew] - PreviousValue)   ///returns number

[mahoneypat]

You can create a measure like this to get the change from previous for any given date.  You didn't list the name of the column with the values, so I used Amount

Change =
VAR thisvalue =
SUM ( Query1[Amount] )
VAR thisdate =
MAX ( query1[ReportDate] )
VAR prevdate =
CALCULATE (
MAX ( Query1[ReportDate] ),
ALL ( Query1[ReportDate] ),
Query1[ReportDate] < thisdate
)
VAR prevvalue =
CALCULATE ( SUM ( Query1[Amount] ), Query1[ReportDate] = prevdate )
RETURN
thisvalue - prevvalue

You can then use this expression to get the average over all the ReportDate values

Avg Change = AVERAGEX(VALUES(Query1[ReportDate]), [Change])

And you can do it over a narrower range with an expression like this

Avg Change Last 10 =
VAR thisdate =
MAX ( Query1[ReportDate] )
RETURN
CALCULATE (
[Avg Change],
FILTER (
ALL ( Query1[ReportDate] ),
Query1[ReportDate] <= thisdate
&& Query1[ReportDate] >= thisdate - 10
)
)

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Greg_Deckler]

@Anonymous - You can just create a new column that calculates the difference from the previous row and then you can just use the default average aggregation on that column. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.

The basic pattern is:

Column = 

  VAR __Current = [Value]

  VAR __Previous = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Value])

RETURN

  __Current - __Previous