Measure to show value change since previous value

[DmitryKo]

The source table is a simple log-type table similar to:

Key	Date	Value	Comment (text)
A	2022-08-01	2
A	2022-08-03	3
A	2022-08-22	4.5
B	2022-08-05	2
B	2022-08-17	1

Note that date (timestamp) values are not guaranteed to be sequential; an important assumption taken in many similar questions is not valid here. Dates can be random as presented in the sample above.

The goal is, to create a DAX measure to calculate:

• average value (straightforward)
• latest value (not so straightforward but doable)
• previous value
• value change between latest and previous value

The result over the sample data above would have to be this:

Metric	Latest value	Previous value	Change since previous
A	4.5	3	1.5
B	1	2	-1

Any suggestion on the "previous value" DAX code?

[v-jianboli-msft]

Hi @DmitryKo ,

Based on your description, I have modified the sample data:

Here is the measure:

average value = AVERAGE('Table'[Value])

latest value = CALCULATE(MAX('Table'[Value]),FILTER('Table',[Index]=MAX('Table'[Index])))

previous value = 
var _a = CALCULATE(MAX([Date]),FILTER('Table',[Index]=MAX([Index])))
var _b = CALCULATE(MAX([Date]),FILTER('Table',[Date]<_a))
return CALCULATE(MAX([Value]),FILTER('Table',[Date]=_b))

value change between latest and previous value = [latest value]-[previous value]

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-jianboli-msft]

Hi @DmitryKo ,

The purpose of creating index is to help find the previous row's value, instead of the previous date. For example, even if the previous row's date is much more bigger than current date, it will still return previous row's value.

If I misunderstood, please give more details to help me clearify your problem.

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DmitryKo]

It's not about using the index or not. It's about logic.

"Change since previous" is NOT the difference between current (as defined by date context filters) value and value at latest possible date available within the dataset. It's the difference between the value at current (as defined by date context filters) value and the value that was available previously - at the preceding date.

The solutions suggested calculate measures for (latest-current), essentially, while what is needed is (current-previous)

[v-jianboli-msft]

Hi @DmitryKo ,

Approve with @daXtreme , You can create an index column to help calculate. Please try:

First, create an index column in power query.

Here is the M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIyMDLSNbDQNTAEcYBYQSlWB03KGMgxxiplBNJhomcKl3RC1meKYiSylKE5kGMIkYoFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, Date = _t, Value = _t, #"Comment (text)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Key", type text}, {"Date", type date}, {"Value", type number}, {"Comment (text)", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type)
in
    #"Added Index"

Output:

Then create these measure:

average value = AVERAGE('Table'[Value])

latest value = CALCULATE(MAX('Table'[Value]),FILTER('Table',[Index]=MAX('Table'[Index])))

previous value = 
var _a = MAX('Table'[Index])
var _b = MAXX(FILTER(ALL('Table'),[Index]<_a),[Index])
return CALCULATE(MAX('Table'[Value]),FILTER('Table',[Index]=_b))

value change between latest and previous value = [latest value]-[previous value]

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[daXtreme]

@DmitryKo 

Why don't you index the rows with an integer that's sequential within each key and follows the Date order? That would then be dead easy to calculate everything you want, wouldn't it?

[DmitryKo]

Index or not, solutions offered still calculate "latest possible" value (as opposed to "latest regarding to the curret date range) for "latest" and "delta between current and latest possble" instead of delta between current and previous values. Not the logic needed.

[DmitryKo]

This metric, specifically Previous value, provides incorrect results over that very sample data above. Specifically, it always returns value that is "previous" related to the LATEST date for the dataset, not that is previous to the CURRENT (specified by "Date") timestamp. As a result, change over the previous value is incorrect as well.

[amitchandak]

@DmitryKo , Try like

Latest value =
var _max = maxx(filter(allselected(Table), [Key] = max(Table[Key]) ), [Date])
return
calculate(Sum(Table[Value]), filter(Table, Table[Date] = _max))

Previous value =
var _max1 = maxx(filter(allselected(Table), [Key] = max(Table[Key]) ), [Date])
var _max = maxx(filter(allselected(Table), [Key] = max(Table[Key]) && [Date] <_max1 ), [Date])
return
calculate(Sum(Table[Value]), filter(Table, Table[Date] = _max))