Forum Discussion

Aark's avatar
Aark
Regular Visitor
4 years ago

Cumulative total with a hard twist

I am new to powerbi and I am unable to solve this question below. Thank you for looking into my question. I have two year month columns(eg: Mar_2021), one is the start month and the other is the end ...
Need Help
Aark's avatar
Aark
Regular Visitor
4 years ago

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's avatar
lbendlin
Icon for Super User rankSuper User
4 years ago

 

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.