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 BI saying that the difference between February and January is '5' (which is the actual value) and the difference between March and February is 15.

I've found below topic, I want to do exactly the opposite of it... Still a newby at this...

http://community.powerbi.com/t5/Desktop/Cumulative-Amounts/m-p/54011

 

Thanks.

  • 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




     

     

     

     

     

14 Replies

  • 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