A rule that affects only subsequent values

[lddiaz]

Hello all, I need some help figuring out how or if Power BI is capable of doing the following:

I am creating a new column, [Exposure] that measures the difference between [Total Cost] and [Amount Billed].
For some specific projects, lets call these "GIM" projects, there is a special rule that says if we reach a total [Exposure] value of $210,000 for all "GIM" projects, then any subsequent project classified within "GIM" will have an [EXPOSURE] of 0. 

Here is an example of what I mean. 


This is only true for "GIM" projects, and all other non-GIM projects should be left unaffected by this rule. The problem I am having is finding a way to let the rule ONLY affect the subsequent "GIM" projects after a total of 210k is reached.

Is this possible in Power BI? Any insight or advice would be appreciated here. 

[Zubair_Muhammad · Accepted solution]

Hi @lddiaz

Try this.

First Add an Index Column from the Query Editor...

Then you might be able to use this column

Exposure =
VAR CumulativeExposure =
    CALCULATE (
        SUM ( Table1[Total Cost] ) - SUM ( Table1[Amount Billed] ),
        FILTER (
            Table1,
            LEFT ( Table1[Project Name], 3 ) = "GIM"
                && Table1[Index] < EARLIER ( Table1[Index] )
        )
    )
RETURN
    IF (
        LEFT ( Table1[Project Name], 3 ) = "GIM"
            && CumulativeExposure < 210,
        Table1[Total Cost] - Table1[Amount Billed]
    )

[Zubair_Muhammad]

@lddiaz

Please see Power Pivot Model in attached Excel file