Percentage difference between the last two entries by date

[markhomer]

Hi all,

 I have a table with the below data, and would like a visual that displays the difference between the two latest values, which in this case would be 1.

Date                     Score

01/01/2019          3

01/02/2019          5

01/03/2019          1

01/04/2019          2

01/05/2019          3

01/06/2019          4

I have a measure that works out the percentage difference between the latest value and the best value (below) but cannot work out how to change this to get my new result?

%Diff Best - Latest score =

VAR __BASELINE_VALUE = [Latest score]

VAR __VALUE_TO_COMPARE = [Best score]

RETURN

    IF(

        NOT ISBLANK(__VALUE_TO_COMPARE),

        DIVIDE(__VALUE_TO_COMPARE - __BASELINE_VALUE, __BASELINE_VALUE)

    )

Any help greatly appreciated!

[lc_finance]

Hi @markhomer ,

DAX does not allow to create a difference based on 'previous value'. You can however create a difference as long as you have a rule for what the previous value should value.

If the previous value is the previous day, you can use the following formula:

Diff with previous day = 
SUM('Table'[Score])-
CALCULATE(SUM('Table'[Score])
, PREVIOUSDAY('Table'[Date].[Date]))

Here is the result:

Let me know if this is what you're looking for.

Regards,

LC

Interested in Power BI finance templates? Check out my blog at www.finance-bi.com

[markhomer]

Thanks LC,

I am hoping it is possible to create a measure that gives me the percentage difference between the last 2 entries.

Is it possible to have some kind 'Max date -1' expression? or maybe 'allexcept' the max date?

Thanks,

Mark

[lc_finance]

Hi @markhomer ,

Yes you can do a MAX to find the last day! Here is the updated formula.

Let me know if it works for you,

LC

www.finance-bi.com

Difference of last 2 days = 
VAR lastday = MAX('Table'[Date])
VAR beforeLastDay = lastday-1

RETURN
CALCULATE(
SUM('Table'[Score])
, 'Table'[Date] = lastday
) -
CALCULATE(
SUM('Table'[Score])
, 'Table'[Date] = beforeLastDay
)