topic Cumulative total with a hard twist in Developer

Cumulative total with a hard twist

Aark — Tue, 30 Aug 2022 07:58:08 GMT

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 month. I have to count another column's values for each month and create a running total. The running total should add count for start month and subtract the count for the end month. Example: Let say we have a columns start month, end month, region and sales

The output should be something like

Please let me know if someone have any idea about this. Would really appreciate your answers. Idea for Making this into a waterfall chart would be great. Note: The month column is of text datatype

Re: Cumulative total with a hard twist

Anonymous — Tue, 30 Aug 2022 09:41:40 GMT

Try these dax：

China Running Total = CALCULATE(SUM('Table'[Sales]),'Table'[Region] = “China”);
Finland Running Total = CALCULATE(SUM('Table'[Sales]),'Table'[Region] = “Finland”);
Brazil Running Total = CALCULATE(SUM('Table'[Sales]),'Table'[Region] = “Brazil”);
Running Total Sales= [China Running Total] + [Finland Running Total] + [Brazil Running Total]

Re: Cumulative total with a hard twist

Aark — Tue, 30 Aug 2022 10:05:32 GMT

Hi. thanks a lot for your suggestion. But doing cumulative sum is not the main problem, subtracting the cumu sum when it ends according to the end date is the problem. I have to take into account both the start date and end date in one column as I have shown in the output table and accordingly add the subtract the sales.

Re: Cumulative total with a hard twist

lbendlin — Tue, 30 Aug 2022 12:50:12 GMT

Looks 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.

Re: Cumulative total with a hard twist

Aark — Tue, 30 Aug 2022 12:54:40 GMT

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.

Re: Cumulative total with a hard twist

lbendlin — Tue, 30 Aug 2022 19:39:59 GMT

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.