Quotas, Cummulative Sum and Remains

[Rate]

Hello all,

I am struggling with some calculations regarding quotas for clients.

I have a table with a list of clients and a maximum amount (quota) they can benefit from discounts. They can buy as much product as they want, but they have a limit to how much of said quantity will have a discount.

I have another table with the list of all the invoices issued to that Client, where I would want to add a column where I calculate the cummulative sum of the sales by date (650 in my example) and that, when the cummulative amount calculated is bigger than the quota assigned to that client (500), it returns the reminder of the quota left to that client at that date (50: calculated as the quota - cummulative sum from previous invoices).

I managed to calculate the cummulative sum but I can't get to make the formula look for the cummulative amount up to the date just before the last invoice.

I paste below the two tables I mentioned, with a column of desired output, as I am sure will help understand better the question.

Any ideas as to how I could calculate this remains on the last invoice?

Thank you very much in advanced!

Client ID	Max Quota
1	500

Invoice ID	Date	Client ID	Income	Cummulative Income	Desired Output
1	01/01/2020	1	100	100	100
2	01/02/2020	1	200	300	300
3	01/03/2020	1	150	450	450
4	01/04/2020	1	200	650	50

[DataZoe]

@Rate Please try this measure:

Cumulative with Quota Measure =
VAR _c = CALCULATE(
    sum( 'Table'[Income] ),
    filter(
        ALLSELECTED( 'Table'[Date] ),
        ISONORAFTER( 'Table'[Date], max( 'Table'[Date] ), DESC )
    )
) 
VAR _q = sum( TableQuota[Max Quota] ) 
VAR _d = _c - _q 

return if( _d > 0, _d, _c )

I wasn't sure if you had a Date table set up or not, so I used the same table date. You can change that to date table date too.

[DataZoe · Accepted solution]

@Rate Oops! I am sorry, I was re-reading your post and realized I missed you wanted it to not take into account today's cumulative, but the earlier one. Try this measure to do that:

Cumulative with Quota Measure =
VAR _c_today = CALCULATE(
    sum( 'Table'[Income] ),
    filter(
        ALLSELECTED( 'Table'[Date] ),
        ISONORAFTER( 'Table'[Date], max( 'Table'[Date] ), DESC )
    )
) 
VAR _c_earlier = CALCULATE(
    sum( 'Table'[Income] ),
    filter(
        ALLSELECTED( 'Table'[Date] ),
        ISONORAFTER( 'Table'[Date], max( 'Table'[Date] ) -1, DESC )
    )
) 
VAR _q = sum( TableQuota[Max Quota] ) 
VAR _d = _c_today - _q 

return if( _d > 0, _q - _c_earlier, _c_today )