cancel
Showing results for
Did you mean:
Regular Visitor

## Get total sum per month from cumulative value

Hi everyone,

I've been struggeling with something. I want a calculated column that calculates the sales of that month based on the cumulitive sales.

ID      Month Year   CumAmount      Sales

1484   2019-jan      120000               -

1484   2019-feb      130000               10000

1484   2019-mar     135000               5000

Unfortunately I don't have a datevalue in the same row, but I do have "2019-jan", "2019-feb" etc. that I have connected with the same data format in a calendar table.

Can someone help me out?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team

## Re: Get total sum per month from cumulative value

For your requirement, please create an index column in Query Editor firstly and then create the calculated column with the formula below.

```Column =
VAR a =
CALCULATE (
MAX ( 'Table1'[CumAmount] ),
FILTER ( ALL ( 'Table1' ), 'Table1'[Index] = EARLIER ( Table1[Index] ) - 1 )
)
RETURN
IF ( ISBLANK ( a ), BLANK (), 'Table1'[CumAmount] - a )
```

Here is the output.

You also could have a reference of my attachement.

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Highlighted
Community Support Team

## Re: Get total sum per month from cumulative value

For your requirement, please create an index column in Query Editor firstly and then create the calculated column with the formula below.

```Column =
VAR a =
CALCULATE (
MAX ( 'Table1'[CumAmount] ),
FILTER ( ALL ( 'Table1' ), 'Table1'[Index] = EARLIER ( Table1[Index] ) - 1 )
)
RETURN
IF ( ISBLANK ( a ), BLANK (), 'Table1'[CumAmount] - a )
```

Here is the output.

You also could have a reference of my attachement.

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Get total sum per month from cumulative value

Hello,

I have the same problem but the other way round...

I have the sales amount per month and would like to show on graph the cumulative.

For exemple :

Feb=Jan+Feb

March=Jan+Feb+March

... and so on.

Thanks,

Regular Visitor

## Re: Get total sum per month from cumulative value

Hi,

You can create a calculated column for that;

```Running Total COLUMN =
CALCULATE (
SUM ( 'TableName'[Value] ),
ALL ( 'Tablename' ),
'TableName'[Date] <= EARLIER ( 'TableName'[Date] )
)```

Announcements

Power BI Super User, Greg Deckler, explains

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 156 members 1,997 guests
Recent signins: