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...
Need Help
Baskar's avatar
Baskar
Icon for Resident Rockstar rankResident 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 .

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
    Icon for Community Champion rankCommunity 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
                )
        )
    )

     

    • Sanjay_N's avatar
      Sanjay_N
      New Member
      4 years ago

      Hello,

      There might be situations where there are many line-items in a single month or single day which are also cumulative.

      Can you help me in solving this issue?