Flag data at Certain Threshold

[Fitin1rb]

Hello - I am looking for some quick help here to meet a tight deadline.  I am looking to flag certain customers in my data set once their aggregate $ amount hits a certain threshold (in this case $3,500).  The data below is what I am looking to acheive in Power BI.  Once the customers aggregate value  hits the threshold I want to flag all deals from that customer going forward (Flagged column).  Thank you in advance for any help provided!

Date	Customer	Contract	$ Value	Flagged
1/1/2019	Customer A	Contract 1	1000
1/1/2019	Customer B	Contract 2	1000
2/1/2019	Customer A	Contract 3	2000
2/1/2019	Customer C	Contract 4	2000
3/1/2019	Customer A	Contract 5	2000	Y
3/1/2019	Customer B	Contract 6	2000	Y
4/1/2019	Customer A	Contract 7	500	Y
4/1/2019	Customer C	Contract 8	500

[parry2k]

@Fitin1rb here is the measure, in your example,Customer B will not be flagged, his cummulative total is less than 3500 

Flagged = 
VAR __value = CALCULATE( SUM( Table7[$ Value] ),FILTER( ALLEXCEPT(Table7, Table7[Customer] ), Table7[Date] <= MAX( Table7[Date] ) ) ) 
RETURN IF( __value > 3500, "Y" )

[Fitin1rb]

@parry2k  this works to an extent, but there are customers that have contracts that are on the same date and if the customer exceeds the limit on that date, it will take ALL of the contracts that are booked on that date and flag them all, not just that contract and future ones.  Is there a way to fix that logic by chance?

[parry2k]

@Fitin1rb which contract will come first in the date??? 

[Fitin1rb]

@parry2k this is what I would expect to see.  Contracts are in sequential numeric order as well.

Date	Customer	Contract	$ Value	Flagged
1/1/2019	Customer A	Contract 1	1000
1/1/2019	Customer B	Contract 2	1000
2/1/2019	Customer A	Contract 3	2000
2/1/2019	Customer C	Contract 4	2000
3/1/2019	Customer A	Contract 5	250
3/1/2019	Customer A	Contract 6	500	Y
3/1/2019	Customer B	Contract 7	2000
4/1/2019	Customer A	Contract 8	500	Y
4/1/2019	Customer C	Contract 9	500
4/1/2019	Customer C	Contract 10	1500	Y

[parry2k]

@Fitin1rb you can add index column in power query and then it is easy

Flagged 1 = 
VAR __value = 
CALCULATE( SUM( Table7[$ Value] ),FILTER( ALLEXCEPT(Table7, Table7[Customer] ), 
Table7[Date] <= MAX( Table7[Date] ) && 
Table7[ContractIndex] <= MAX( Table7[ContractIndex] ) 
) 
) 
RETURN IF( __value > 3500, "Y" )

[Fitin1rb]

@parry2k  thank you.  I have adjusted the measure, however it is taking forever to run so I am not sure this solution will work for me if it continues to take this long.

[parry2k]

@Fitin1rbtry following changes and see if it performs better

Flagged = 
VAR __date = MAX( Table7[Date])
VAR __contractIndex = MAX( Table7[ContractIndex] )
VAR __value = 
CALCULATE( 
SUM( Table7[$ Value] ), 
ALLEXCEPT(Table7, Table7[Customer] ), 
Table7[Date] <= __date, 
Table7[Contract] <= __contractIndex
) 
RETURN IF( __value > 3500, "Y" )

[Fitin1rb]

@parry2k  unfortunately no improvement on performance with the updated measure

[parry2k]

@Fitin1rb how big is this dataset?

[Fitin1rb]

@parry2k  30k rows

[parry2k]

@Fitin1rb that is nothing, it should really well, not sure what is going on there, were it working fine before we added contract condition for flag, let's try without contract and see if it has good speed.

[Fitin1rb]

@parry2k  it works without the contract index, but still very slow and does not provide the accurate output I am looking for, 

I was not even showing an output with the contract index in the measure, it just kept spinning and thinking when I tried to put the measure into a table or graph.