Measure the Sum of column less then cretiria

[xl0911]

I have this table (Tbl_Sales)  :

SalesID | ClientID | Date       | Amount
-----------------------------------------------
6           | 1             | 01/01/2021       | 100
8           | 1             | 01/01/2021       | 150
3           | 1             | 02/01/2021       | 50
1           | 1             | 09/01/2021       | 200
5           | 2             | 03/01/2021       | 100 
9           | 2             | 06/01/2021       | 50
7           | 2             | 07/01/2021       | 150
3           | 2             | 07/01/2021       | 50
4           | 2             | 07/01/2021       | 200

* (Date's are in dd/mm/yyyy format)

Also, I have this table Tbl_Clients

ClientID  |  Limit

--------------------

1           |    220

2           |    300

The rules are that I need a measure that gets every month the last Sum of each client BUT if the sum amount of the client is over the limit I need the amount before of the sum that caused to passed the limit, Exaple's:

Client ID 1, at Jan 2021, I expecte to get 100, because first I check if the amount is over the limit, so I added the 100 (SalesID 6) then I check if next amount is over the limit, if so I dont add it to the sum.

Client ID 2, at Jan 2021, I expecte to get 200 becouse i sum the SalesID (5,9,3) = 200 and if I add SalesID 4 it will pass the limit.

Importent Notes:

1. The order of adding sum amount is by the date, and if there are more then one date, the order is by the SalesID.

2. The limit is per month, so every month the client starts from 0.

(edit:)  3. I have a date table if needed 

(edit2:) 4. The sales table is with 3 milion rows, so if high Performence is possible I will be very good.

Hope It was clear enough 🙂

[ahmedoye]

What you could do that should work (pending any better solution) is:

1. Create a Calculated Column for the Cummulative Amount like:
   
   
   Cummulative Amt =

   VAR MY =

   FORMAT ( Tbl_Sales[Date], "mmyy" )

   VAR Limit =

   RELATED ( Tbl_Clients[Limit] )

   VAR DayOfYear =

   VALUE ( Tbl_Sales[Date] - STARTOFYEAR ( Tbl_Sales[Date] ) )

   VAR SalesIDMod = ( DayOfYear * 10 ) + Tbl_Sales[SalesID]

   RETURN

   SUMX (

   FILTER (

   ALL ( Tbl_Sales ),

   Tbl_Sales[ClientID] = EARLIER ( Tbl_Sales[ClientID] )

   && Tbl_Sales[Date] <= EARLIER ( Tbl_Sales[Date] )

   && FORMAT ( Tbl_Sales[Date], "mmyy" ) = MY

   && (

   VALUE ( Tbl_Sales[Date] - STARTOFYEAR ( Tbl_Sales[Date] ) ) * 10

   ) + Tbl_Sales[SalesID] <= SalesIDMod

   ),

   Tbl_Sales[Amount]

   )

   2. Create your Measure as:
   

   Cummulative =

   VAR ClientLimit =

   SELECTEDVALUE ( Tbl_Clients[Limit] )

   VAR SelectedClientID =

   SELECTEDVALUE ( Tbl_Clients[Client ID] )

   VAR SelectedMonth =

   FORMAT ( SELECTEDVALUE ( Tbl_Sales[Date] ), "mmyy" )

   VAR NormalCumm =

   MAX ( Tbl_Sales[Cummulative Amt] )

   VAR AbnormalCumm =

   MAXX (

   FILTER (

   ALL ( Tbl_Sales ),

   Tbl_Sales[Cummulative Amt] < RELATED ( Tbl_Clients[Limit] )

   && Tbl_Sales[ClientID] = SelectedClientID

   && FORMAT ( Tbl_Sales[Date], "mmyy" ) = SelectedMonth

   ),

   Tbl_Sales[Cummulative Amt]

   )

   RETURN

   IF ( NormalCumm > ClientLimit, AbnormalCumm, NormalCumm )

   I hope that helps. You can PM me and I'll see if I can attach the PBIX file I used for you.

   If that solution works for you, kindly mark as solution to enable other community memebers who may have similar challenges find it easily.

[xl0911]

Thank you so much for trying to help I really appreciate it but unfortunately this code was not helpful, not because the result is incorrect but because of a performance with millions of lines (my file is already pretty cluttered).
After entering the code, each action I took on the file took several minutes of waiting.

[ahmedoye]

When an amount will make the sum pass the limit, you want to return the latest sum without passing the limit, or you want to return the last amount instead. For example, if limit is 200 and you have 60 in 4 places, adding the 4th 60 will make it go pass the limit. So do you want to return the 3rd 60, or you want to return 180?

[xl0911]

Return the latest sum without passing the limit (180 in your exampl)