Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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...)

6 REPLIES 6
Advocate I

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
)
)
Resident Rockstar

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 .

New Member

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.

Frequent Visitor

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.

Community Champion

@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
)
)
)```

Lima - Peru
New Member

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?

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors