Forum Discussion

JurriaanQEL's avatar
JurriaanQEL
Regular Visitor
8 years ago
Solved

Remove cumulative total

Hi,    Every month I receive a file with updated numbers. One of the columns is cumulative, e.g.: January     -    20 February   -    25 March       -    40   I want to have a table in Power ...
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Hi JurriaanQEL

     

    Please see the attached file here.

    Hope this helps. Here are the steps I performed

     

    As you said we need an assistant field to undo the Cumulatives. So
    First I created a "Parameter Table" to get "month number" in our Main Table

    Then we can use this Calculated Column to get Monthly figures

    Monthly Figure =
VAR PreviousMonthValue =
    CALCULATE (
        SUM ( Table1[Cumulative] ),
        FILTER (
            Table1,
            Table1[Year] = EARLIER ( Table1[Year] )
                && Table1[MonthNumber]
                    = EARLIER ( Table1[MonthNumber] ) - 1
        )
    )
RETURN
    Table1[Cumulative] - PreviousMonthValue




     

     

     

     

     

Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

Hi@ JurriaanQEL

 

Could you please copy paste sample data just like in the post you referred to?

Do you have other columns beside month and amount?

  • JurriaanQEL's avatar
    JurriaanQEL
    Regular Visitor
    8 years ago

    Hi Zubair_Muhammad Thanks for your quick reply.

    Below the data table that comes from Power BI.

    The source data is not one table. Every month I receive a file, which is added to the source data. In this data there is a field called in This Month. You can see that for example March 2016, this value was: 3080, where in April 2016 this value was 35755. The difference between the 2 (32675) is the non cumulated number for April. Somehow I want to be able to see this figure somewhere (I've tried it with work arounds, but I don't have any other source fields (date) that can help me here). 

     

     

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      8 years ago

      Hi JurriaanQEL

       

      Please see the attached file here.

      Hope this helps. Here are the steps I performed

       

      As you said we need an assistant field to undo the Cumulatives. So
      First I created a "Parameter Table" to get "month number" in our Main Table

      Then we can use this Calculated Column to get Monthly figures

      Monthly Figure =
VAR PreviousMonthValue =
    CALCULATE (
        SUM ( Table1[Cumulative] ),
        FILTER (
            Table1,
            Table1[Year] = EARLIER ( Table1[Year] )
                && Table1[MonthNumber]
                    = EARLIER ( Table1[MonthNumber] ) - 1
        )
    )
RETURN
    Table1[Cumulative] - PreviousMonthValue




       

       

       

       

       

      • JurriaanQEL's avatar
        JurriaanQEL
        Regular Visitor
        8 years ago

        Thanks a lot! Gonna work on this :manhappy: