Cumulative sum with VALUES filter issue?

[kattlees]

I have a deduction table.... I'm trying to get a projected total of the deduction value for the year up to 5000. Once it hits 5000 it should stop.

So I have the following:

A column:

DedValue=if(table[code]="125CLD",table[deductionamount],0)   gets me the deduction value for each pay period so far

A measure

DedValueTotal=sum(table[DedValue]) - gets me the deduction value so far

To get projected value = I have 

ProjValueActual = ([DedValueTotal]/[PayDatesDone])*[TotalPayDates]

Now i want to do a projected for the year with a maximum of 5000.00

I was thinking of a column of

Proj Ded value = if([ProjValueActual > 5000,5000,[ProjValueActual]) but this is not limiting at 5000. It just shows the ProjValueActual

Example:

Employee      Code                Deductionamount         Date

Jim                  125CLD              500                             1/15/17

Sue                  125CLD                 50                            1/15/17

Jim                   125CLD              500                             1/30/17

Sue                  125CLD                 50                            1/30/17

Jim                   125CLD              500                            2/15/17

Sue                   125CLD                50                            2/15/17

Results should be

Employee                 DeductionAmount           ProjValueActual            Proj Ded Value

Jim                           1500                                        12000                         5000    (need to stop at max value)

Sue                           150                                           1200                         1200

[MarkS]

Hi @kattlees,

Would this measure work

ProjDedValue = MIN(5000,[ProjValueActual])

[kattlees]

Gives me the same thing.  😞

[Greg_Deckler]

What are your formulas for PayDatesDone and TotalPayDates?

[kattlees]

In Pay Dates Table

PayDatesDone=sum(PayDates[CountPayDates])

CountPayDates=if(PayDates[PayDate]<today(),1,0)

PayDatesLeft is a value we enter manually

TotalPayDates = PayDatesDone+PayDatesLeft