Forum Discussion
Calculate Difference of a balance for users; between dates.
Hi all!
Please see below picture. So I guess my problem is kinda straight forward but can't find the answer.
I might be searching for the wrong terms. Apologies.
Anyhow. As you can see below the first column "link" is a unique "User" (lets say) and the User has a balance in the last column "Claimed Essence". This balance increases for everyday as can see with the "DATE" and "Claimed Essence" Value.
I have managed to derive a calculated column for the "previous date" for the balance value (not sure if I need it).
What I want to do is to calculate the DIFFERENCE (The increased value) between the balance at the previous date for this "LINK" vs the DATE to get the (new) "INCREASED VALUE" that is the "added" value to the balance.
What would the formula be for that calculated column "INCREASED VALUE"?
Exmaple outcome:
Row 2 (from top) would say: 2 (844-842 = 2)
Row 3 (from top) would say: 8 (852-844 =8 )
All fields are in the same data table just like visualized but there are many different unique "links".
Each Unique "link" only appears once on every date with its own balance. Hence granularity of the table is per "link" per "date".
See it like "accounts" at a bank with increased "balance" for each day and the question is to calc the added value with a calculated column.
Thanks a lot guys!
8 Replies
- serpiva64
Solution Sage
Hi,
if this is what you need
you can:
- insert 2 column index (one starting from 0 and one from 1)
- merge query
- expand
- sort rows on Index ascending (starting from 0)
- and finally add column with difference
If this post isuseful to help you to solve your issue consider giving the post a thumbs up and accepting it as a solution !
Hi !
Thank you for giving a go to support me! 😃 I did not manage to reach the right answer. See picture below.
I think it is because it needs to consider that I have many several unique "links" not only one.
I have uploaded my rawdata table here that would perhaps clarify what the data looks like:
https://easyupload.io/oabeye- serpiva64
Hi,
you can obtain this
by applying this steps
let
Source = Excel.Workbook(File.Contents(___________), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Added Index" = Table.AddIndexColumn(Table1_Table, "GenIndex", 1, 1, Int64.Type),
#"Changed Type" = Table.TransformColumnTypes(#"Added Index",{{"Date", type date}, {"Tier", Int64.Type}, {" Class", Int64.Type}, {" Tiles", Int64.Type}, {" Claimed Essence", Int64.Type}, {" Promised Essence", Int64.Type}, {" Has Holo", type any}, {" Country", type text}, {" Location", type text}, {" Description", type text}, {" For Sale", Int64.Type}, {"Link", type text}, {"EDC", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {" Country", "Link"}, {{"Count", each _, type table [Date=nullable date, Tier=nullable number, #" Class"=nullable number, #" Tiles"=nullable number, #" Claimed Essence"=nullable number, #" Promised Essence"=nullable number, #" Has Holo"=text, #" Country"=nullable text, #" Location"=nullable text, #" Description"=nullable text, #" For Sale"=nullable number, Link=nullable text, EDC=nullable text, GenIndex=number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count],"Index")),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Table.AddIndexColumn([Custom],"Index1",1)),
#"Expanded Custom.1" = Table.ExpandTableColumn(#"Added Custom1", "Custom.1", {" Claimed Essence", " Class", " Description", " For Sale", " Has Holo", " Location", " Promised Essence", " Tiles", "Date", "EDC", "GenIndex", "Index", "Index1", "Tier"}, {" Claimed Essence", " Class", " Description", " For Sale", " Has Holo", " Location", " Promised Essence", " Tiles", "Date", "EDC", "GenIndex", "Index", "Index1", "Tier"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom.1",{"Count", "Custom"}),
#"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"Link", "Index"}, #"Removed Columns", {"Link", "Index1"}, "Removed Columns", JoinKind.LeftOuter),
#"Expanded Removed Columns" = Table.ExpandTableColumn(#"Merged Queries", "Removed Columns", {" Claimed Essence"}, {" Claimed Essence.1"}),
#"Sorted Rows" = Table.Sort(#"Expanded Removed Columns",{{"GenIndex", Order.Ascending}}),
#"Added Conditional Column" = Table.AddColumn(#"Sorted Rows", "Difference", each if [#" Claimed Essence.1"] = null then 0 else [#" Claimed Essence"]-[#" Claimed Essence.1"]),
#"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column",{"Index", "Index1", " Claimed Essence.1"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns1",{{" Claimed Essence", Int64.Type}, {"Difference", Int64.Type}, {" Promised Essence", Int64.Type}})
in
#"Changed Type1"If this post isuseful to help you to solve your issue consider giving the post a thumbs up and accepting it as a solution !
