How to calculate change comparing to previous value (not date)

[markostalnacke]

Hi, I searched the forum without solution.

I have a table with values being filled generally twice a week. I need to be able to count the change in amount with these last two filled dates. ie.

Date	City	Amount
18.4.2020	Tokio	500
21.4.2020	Tokio	550
21.4.2020	Helsinki	250
24.4.2020	Tokio	300
24.4.2020	Helsinki	200

What I would like to get:

City	18.4.2020	Change	21.4.	Change	24.4.	Change
Helsinki			250		200	-50
Tokio	500	0	550	50	300	-200

Any ideas? Thanks.

[Robert-RK · Accepted solution]

You can try following solution:

1. create measure 'Previous order'

Previous order =

var PriorDate = CALCULATE(MAX(city[Date]),

FILTER(ALL(city[Date]), city[Date] < MAX(city[Date])))

RETURN

CALCULATE(SUM(city[Amount]),

FILTER(ALL(city[Date]), city[Date] = PriorDate))

2. and then measure that you need:

Change = IF(ISBLANK([Previous order]),BLANK(),SUM(city[Amount]) - [Previous order])

[markostalnacke]

Great Robert! Works!

[harshnathani]

Hi @markostalnacke ,

You can use this in the Query Editor.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrTQN9E3MjAyUNJRCsnPzswH0qYGBkqxOtFKRoZY5Ewx5TxSc4oz87IzgUwjmLQJplZjA0w5ZK0g6VgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ate = _t, City = _t, Amount = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ate", type text}, {"City", type text}, {"Amount", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"ate", "Date"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type text}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Date]), "Date", "Amount", List.Sum),
#"Added Custom" = Table.AddColumn(#"Pivoted Column", "Change1", each [#"21/4/2020"]-[#"18/4/2020"]),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Change2", each [#"24/4/2020"]-[#"21/4/2020"]),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom1",{"City", "18/4/2020", "21/4/2020", "Change1", "24/4/2020", "Change2"})
in
#"Reordered Columns"

Regards,

Harsh Nathani

[markostalnacke]

Hi Harsh, 

Your solution is assuming the dates are always those. I can't use it that way, because we will have dates (and values) for months and dates unfortunately can differ. ie. some unit misses a date etc. 

This would do the trick, but would need a create function PREVIOUSVALUE 🙂
Change=CALCULATE(Sum(Table[Amount]);PREVIOUSDAY(Table[Amount]))