Finding Difference in Values Between Last Date and Second to Last Date

[jbaucke]

Using a data set with a structure like below, I am trying to determine if a value related to a certain field, in this case id, is either rising or falling. We are using the most recent value and the value prior to the last one to determine that. In the example below, the latest value is 2 and the previous value is 0, so the change metric on this would equal 2, so the value would be defined as "rising". 

personid	Date		Value
10	1/14/2020		0
10	1/29/2020		1
10	2/13/2020		1
10	2/28/2020		2
10	3/14/2020		1
10	3/29/2020		0
10	4/13/2020		2

Here is what I have tried so far.

Rising Falling Stable =

VAR date_a = VALUES(facttable[date])

VAR person_id = VALUES(facttable[personId])

VAR date_rank = CALCULATE (RANK.EQ ( date_a, facttable[date], DESC ), FILTER ( ALL (facttable), facttable[personId] = person_id ))

VAR latest_value = CALCULATE(VALUES(facttable2[OverallValue]), FILTER(facttable, date_rank=1))

VAR previous_value = CALCULATE(VALUES(facttable2[OverallValue]), FILTER(facttable, date_rank=2))

VAR value_difference = latest_value-previous_value

RETURN

value_difference

This results in the following table being displayed:

personid	Date	Value	date_rank	latest_value	previous_value	Rising Falling Stable
10	1/14/2020	0	7
10	1/29/2020	1	6
10	2/13/2020	1	5
10	2/28/2020	2	4
10	3/14/2020	1	3
10	3/29/2020	0	2		0	0
10	4/13/2020	2	1	2		2

Do anyone know how I can get the previous and current metrics into the same row in this table so I can get a change since previous date metric to be showing in the Rising Falling Stable column? 

[mahoneypat · Accepted solution]

Here is a measure expression you can use in a Table visual with the PersonID column to get your desired result.

Trend =
VAR latestdate =
    MAX ( 'Cases'[Date] )
VAR prevdate =
    CALCULATE ( MAX ( 'Cases'[Date] ), 'Cases'[Date] < latestdate )
VAR latestvalue =
    CALCULATE ( SUM ( 'Cases'[Value] ), 'Cases'[Date] = latestdate )
VAR prevvalue =
    CALCULATE ( SUM ( 'Cases'[Value] ), 'Cases'[Date] = prevdate )
RETURN
    SWITCH (
        TRUE (),
        latestvalue > prevvalue, "Rising",
        latestvalue < prevvalue, "Falling",
        "Stable"
    )

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

Regards,

Pat