Forum Discussion
Running total to date
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
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"
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
Hi Mariusz,
Thanks for the below. This is perfect, but how did you do this, can you share the measure.
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.
LinkedInThank you so much....