cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TimHellegers Regular Visitor
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
Community Support Team
Community Support Team

Re: Get total sum per month from cumulative value

Hi @TimHellegers ,

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.

ou.PNG

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
Community Support Team
Community Support Team

Re: Get total sum per month from cumulative value

Hi @TimHellegers ,

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.

ou.PNG

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.
aer98 Frequent Visitor
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.

Could you please help me ?

 

Thanks,

Highlighted
TimHellegers Regular Visitor
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] )
)

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 51 members 1,004 guests
Please welcome our newest community members: