Forum Discussion
Cumulative total with a hard twist
Super UserLooks like you have overlapping periods per region too. Not clear if the Sales value should be applied for all months in the interval (bar the end month). Please clarify.
Hi, Thanks for your reply. The sales value should increase based on the start month and should decrease based on the end month. For example, for the first row in the input table, for china the sales value increases by 400 on 2021 jan and should decrease by the same 400 for the month of 2022 feb as this is the end month. Hope this clarifies your question a bit. Let me know incase you need more information.
- lbendlin
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJU0lEygjCNgEznjMy8RCBtYmCgFKsTDZMCqTJEUuaWmZeTmJcCEsWv0KkosSozB0mdJUKdMaa1xnhVIWw1QrUVJGkCYRpjVYdkCMx1xhiuiwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Start = _t, End = _t, Region = _t, Sales = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start", type date}, {"End", type date}, {"Sales", Currency.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", (k)=> List.Generate(()=>0,each Date.AddMonths(k[Start],_) < k[End], each _ + 1,each Date.AddMonths(k[Start],_))), #"Expanded Dates" = Table.ExpandListColumn(#"Added Custom", "Date"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Dates",{{"Date", type date}}) in #"Changed Type1"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
Then you can create a matrix visual
Note that "running total" generally has a different meaning.