Measure that compares parameter from last month

[william_fu]

Need a measure to account for values that had a change from "100" in the previous month to a different value in the current month:

NroProposta	DtReferencia	LLR.PdCorrente	Saldo
410613	1/12/2017	16,3	165906
410613	1/1/2018	16,3	167059
356907	1/12/2017	100	9339
356907	1/1/2018	36,75	4486
352529	1/12/2017	6,1	6409
352529	1/1/2018	2,5	6433

Considering the table above, NroProposta 356907 is eligible because the LLR.PdCorrente went from 100 in Dec/17 to 36,75 in Jan/18

That said, I want the measure to return the sum of Saldo for this criteria (9339 when evaluated in Jan/18)

There's some more sample data and the desired result in the file attached, (the two contracts that fit this criteria are 356907 and 351972)

Thanks in advance!

[sunny_talwar]

I don't see any attachment? Did you forgot to add?

[william_fu]

Sorry, just edited the post to include attachment

[sunny_talwar · Accepted solution]

Try this

Sum(Aggr(If(Above([LLR.PdCorrente]) = 100 and [LLR.PdCorrente] <> 100, Above(Saldo)), NroProposta, (DtReferencia, (NUMERIC)))) / Sum(Aggr(If(Above([LLR.PdCorrente]) = 100, Above(Saldo)), NroProposta, (DtReferencia, (NUMERIC))))

[william_fu]

Thank you Sunny, this works great in the sample file but I'm running into some performance issues in my actual app.

Do you see any way to pull this off without using Aggr?

[sunny_talwar]

Use The As-Of Table