Forum Discussion
Running total to date
Hello,
I have the below data where the value is running total, need help in creating a a column where it will populate the date wise
If the date increases daily, you could create calculated columns below:
earlier = CALCULATE ( SUM ( 'Table'[Running Value] ), FILTER ( 'Table', 'Table'[Country] = EARLIER ( 'Table'[Country] ) && 'Table'[Date] = EARLIER ( 'Table'[Date] ) - 1 ) ) daily total = [Running Value]-[earlier]As tested, if i understand you correctly, your second visual has some wrong data due to teh calculation rule.
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Thank you so much, it work out to perfection.
This solution was created using Power Query as you have posted your question in PQ section, the script dose Include the M code in Added Column step of the script.
Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
LinkedIn
14 Replies
I do not understand. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Greg,
I have the below columes- Country/ Date/ Running value.... I wanted create a new column Daily figure, which will be the total value of that day.
Country Date Running Value Daily Figure(need to be calculated) Japan 1/20/20 1 1 India 1/20/20 5 5 Japan 1/21/20 3 2 India 1/21/20 5 0 Japan 1/22/20 8 5 Please see the below script or attached file
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kosSMxT0lEyMjAy0DfUNzIAsg2VYnWilTzzUjIT0WRMwTLoegyBbGOsegxx6jECsi2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Country = _t, Date = _t, #"Running Value" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}, {"Date", type date}, {"Running Value", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Country"}, {{"tbl", each _, type table [Country=text, Date=date, Running Value=number]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let tbl = Table.Sort( [tbl], "Date" ), count = Table.RowCount( tbl ), lst = { 0..count -1 }, transform = List.Transform( lst, (i) => [ Value = try tbl{ i }[Running Value] - tbl{ i-1 }[Running Value] otherwise tbl{ i }[Running Value] ] & tbl{ i } ) in transform ), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"tbl"}), #"Expanded Custom" = Table.ExpandListColumn(#"Removed Columns", "Custom"), #"Expanded Custom1" = Table.ExpandRecordColumn(#"Expanded Custom", "Custom", {"Value", "Date", "Running Value"}, {"Value", "Date", "Running Value"}), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom1",{{"Value", Int64.Type}, {"Date", type date}, {"Running Value", Int64.Type}}) in #"Changed Type1"Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
LinkedIn
Hi Greg,
I tried all could, but stiil nothing worked, my request is very straight forward, i need to breakdown the incremental value on date wise in Daily Total Column. Appreciated your help
Country Date Incremental Total Daily total Afghanistan 1/20/2020 5 5 Albania 1/20/2020 10 10 Algeria 1/20/2020 2 2 Afghanistan 1/21/2020 10 5 Albania 1/21/2020 20 10 Algeria 1/21/2020 2 0 Afghanistan 1/22/2020 12 2 Albania 1/22/2020 40 30 Algeria 1/22/2020 4 4 Afghanistan 1/23/2020 15 3 Albania 1/23/2020 42 12 Algeria 1/23/2020 17 13 Afghanistan 1/24/2020 22 7 Albania 1/24/2020 42 30 Algeria 1/24/2020 23 10 Afghanistan 1/25/2020 50 28 Albania 1/25/2020 43 13 Algeria 1/25/2020 26 16 If the date increases daily, you could create calculated columns below:
earlier = CALCULATE ( SUM ( 'Table'[Running Value] ), FILTER ( 'Table', 'Table'[Country] = EARLIER ( 'Table'[Country] ) && 'Table'[Date] = EARLIER ( 'Table'[Date] ) - 1 ) ) daily total = [Running Value]-[earlier]As tested, if i understand you correctly, your second visual has some wrong data due to teh calculation rule.
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
