Cumulative Sum by 2 columns

[kbabu57]

Hi Folks and @Fowmy @Jihwan_Kim 

I have a requirement where i have to get cumulative sum by 2 columns. The below is the sample data

The thing is Final Amount is coming by Customer Number and Year 

Year	Customer Number	Price Amt	Diff	Balance Pay	Final Amount
2015	123	$1000	-50	$500	$450
2016	123	$1000	100	$600	-$50
2017	123	$1000	200	$200	-$50
2015	789	$2000	800	$100	$2700
2016	789	$2000	700	$300	$3100
2017	789	$2000	600	$400	$3700

I want to get Final Amount calculation as below:
1sr row calculation =  $1000 +(-50) - $500 = $450

2nd row calculation = $1000 + ((-50)+100) - ($500+$600) = -50

3rd row calculation = $1000 + (-50+100+200) - ($500+$600+$200) = -50

4th row calculation = $2000+800 - $100 = $2700

5th row calculation = $2000+(800+700) - ($100+$300) = $3100

6th row calculation = $2000 + (800+700+600) - $400 = $3700

[Anonymous · Accepted solution]

Hi @kbabu57 

@Ashish_Mathur Good share!

For your question, here is the method I provided:

Here's some dummy data

"Datas"

You can create a measure. Group by customer number, and add up "Diff" and "Balance Pay".

Final Amount = 
CALCULATE (
    SUM ('Datas'[Price Amt]),
    FILTER (
        ALLEXCEPT ('Datas', 'Datas'[Customer Number]),
        'Datas'[Year] = MAX ('Datas'[Year])
    )
)
+ CALCULATE (
    SUM ('Datas'[Diff]),
    FILTER (
        ALLEXCEPT ('Datas', 'Datas'[Customer Number]),
        'Datas'[Year] <= MAX ('Datas'[Year])
    )
)
- CALCULATE (
    SUM ('Datas'[Balance Pay]),
    FILTER (
        ALLEXCEPT ('Datas', 'Datas'[Customer Number]),
        'Datas'[Year] <= MAX ('Datas'[Year])
    )
)

Here is the result

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kbabu57]

Wow.. This is even better. Thank you Nono Chen

[kbabu57]

Hi Ashish @Ashish_Mathur 

1st of all , Thank you so much for replying my thread. Sorry for delayed reply as i am trying out with any options & combinations with the above pbi file which you attached. Seems like, you created a extra table called "Date" and developed the logics to achieve the requirement. Is there any way without creating extra "date" and build the logic with the existing data ?

I will take a step back. I have only customer numbers as below and want to achieve the requirement.Please provide your thoughts how can we get with the below data

Customer Number	Price Amt	Diff	Balance Pay	Final Amount
abc123	$1000	-50	$500	$450
abc123	$1000	100	$600	-$50
abc123	$1000	200	$200	-$50
def567	$2000	800	$100	$2700
def567	$2000	700	$300	$3100
def567	$2000	600	$400	$3700

Thanks in advance

[Ashish_Mathur]

You are welcome.  You must create a Calendar Table.

[kbabu57]

Hi Ashish @Ashish_Mathur 

Thank you. Much appreciated for your help. However, i just tried without creating extra Calendar table, with the existing data that has date column, got the required output as well 🙂

[kbabu57]

Hi Friends, Could you please help how to write the DAX measure for the above requirement

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.