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

Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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]

    • Aark's avatar
      Aark
      Regular Visitor
      4 years ago

      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.

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    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.

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