Best performing way to do this?

[sreachard]

I've been fighting with calculate for awhile (and losing!). The pattern i'm trying to solve is something common to almost every measure I would write.
I need to sum Premium for insurance policies for a selected time period.
the policies have multiple endorsements that caould be in effect for the time period.
I need only the maximum endorsement for a period.
the measure (or measures) will need to roll up to the account and business.

example:
time period january and february 2010

policy endorse effdate trans amt Premium
513 0 2010-01-01 422491.00 422491
513 1 2010-01-01 801.00 423292
513 2 2010-01-01 0.00 423292
513 3 2010-02-27 -799.00 422493
513 4 2010-03-11 628.00 423121

so for the policy above endorsement 3 is the max for the jan feb 2010 timeperiod and the premium is 422493

I've tried qite a few ways to do this and some even worked but were very slow

i think i need to create a measure with calculate to get the max endorsement for the period
then use sumx with that measure filtering the policies, i did get a variation of that to almost work but I couldn't get a distinct list of policies, it calculated for each policy and endorsement. If anyone can give me some suggestions i'd really apprecieate it, I'm about ready to give up on this working for us.

here is a link to a blank pivot with sample data:

http://sdrv.ms/Q7Us3U


Thanks

Scott

 

[JavierGuillen]

Hi Scott,

Have you tried a calculation like this one?

CALCULATE( VALUES(Table1[Premium]) , FILTER(Table1, Table1[endorse] = MAX(Table1[endorse]) ) )

if you place, for example, effdate as a slicer, then select jan and feb, and then play policy on pivot table rows, the expression above will give you the value of 422493 - the premium for the max policy in that period.

Javier Guillen

 

[sreachard]

Wow, that works much better, but i still have one issue with it.

I created a measure
PolicyPremium :=CALCULATE(values(fact[annualpremium]) , FILTER(policy, policy[ENDORSEMENTNBR] = MAX(policy[ENDORSEMENTNBR]) ) )

created a second measure with sumx (i need that to get a row context right?)
SumPolicyPremium :=sumx(DISTINCT(policy[Policy]),[PolicyPremium])

created a third measure to gilter the policies:
ExpiringPremium :=calculate([sumPolicyPrem],filter(policy,policy[POLICYEXPIRATIONDATE]>=date(2012,07,01) &&policy[POLICYEXPIRATIONDATE]<=date(2012,07,31)))

that mostly works, but when i include policy in my rowlabels i get nothing?
I also tried to add a count of distinct policies, that killed performance.

Link to updaeted workbook
http://sdrv.ms/QogMnS


Thanks!!

 

[JavierGuillen]

hi sreachard

The measure [ExpiringPremium] is actually computing a value at the policy level. The problem is you have your policy list filtered for 2 policies that fall out of the expiration date.

Try clicking on the drop down next to 'row lables' in the pivot table, then on the 'select a field' dropdown select 'policy' and then click on the red X for 'clear filter'. You will now see numbers at the policy level.