Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
You can try following solution:
1. create measure 'Previous order'
You can try following solution:
1. create measure 'Previous order'
Great Robert! Works!
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
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]))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |