Forum Discussion

MrBertie's avatar
MrBertie
Regular Visitor
9 years ago

Convert Monthly Cumulative (Running) Total to Monthly Absolute Totals

I have a dataset with monthly cumulative totals for all financial columns.  Is there an easy way to convert the cumulative total in one column into an absolute monthly total instead?

 

(Yes, I have done the required couple of hours of googling, but I can't seem to find it or maybe I'm just asking the wrong question...)

Need Help

6 Replies

  • Baskar's avatar
    Baskar
    Resident Rockstar
    9 years ago

    For my uderstanding  do u want keep single record for month ? 

     

    ya we can do. Share some sample data with expecting result .

    i will help u to achieve your goal my dear friend .

    • Sanjay_N's avatar
      Sanjay_N
      New Member
      4 years ago

      Hello friend,
      This is exactly what i want.

      For example, consider the below image.

       

      The data i have is in the cumulative form,
      But i want it to be absolute, to transform the cumulative column using Power Bi into absolute column as in the image.

       

      I hope you understood, please help.

    • MrBertie's avatar
      MrBertie
      Regular Visitor
      9 years ago

      In my dataset I have the month number and Cumulative Total, I would like to calculate the absolute monthly spending as in the example below.

       

      • Vvelarde's avatar
        Vvelarde
        Community Champion
        9 years ago

        MrBertie

         

        Hi, You can create a new column in your table:

         

        Absolute =
VAR MonthSpent = Spending[Month]
RETURN
    CALCULATE (
        SUM ( Spending[Cumulative] )
            - CALCULATE (
                SUM ( Spending[Cumulative] ),
                ALL ( Spending ),
                Spending[Month]
                    = MonthSpent - 1
            )
    )

        If your data miss the 4 month: You can add a IF

         

        Absolute =
VAR MonthSpent = Spending[Month]
RETURN
    IF (
        COUNTROWS ( FILTER ( ALL ( Spending ); Spending[Month] = MonthSpent - 1 ) )
            > 0,
        CALCULATE (
            SUM ( Spending[Cumulative] )
                - CALCULATE (
                    SUM ( Spending[Cumulative] ),
                    ALL ( Spending ),
                    Spending[Month]
                        = MonthSpent - 1
                )
        ),
        CALCULATE (
            SUM ( Spending[Cumulative] )
                - CALCULATE (
                    SUM ( Spending[Cumulative] ),
                    ALL ( Spending ),
                    Spending[Month]
                        = MonthSpent - 2
                )
        )
    )

         

  • kateshepardphd's avatar
    kateshepardphd
    Advocate I
    3 years ago

    I had to modify the formula -- maybe this will help someone:

    Absolute =
    VAR MonthSpent = TableName[Date].[MonthNo]
    RETURN
        CALCULATE (
            MIN(TableName[CumulativeColumn])
                - CALCULATE (
                    MIN(TableName[CumulativeColumn]),
                    ALL ( TableName ),
                    TableName[Date].[MonthNo]
                        = MonthSpent - 1
                )
        )