cancel
Showing results for
Search instead for
Did you mean:
Resolver II

## Measure based on another measure

Good afternoon,

I am just learning PBI and paste two measures as below.

The first measure gets right result. The second meaure is based on the first measure but did not go through.

I get the Uneared Premium though creating a calcuated column for InForce Premium but I prefer a measure solution for Unearned Premium.

I would like to thank you for any help.

Dennis

M1 InForce Premium =
CALCULATE (
SUM ( Coverage[ANNUAL_PREMIUM] ),
FILTER ( Coverage, Coverage[EFFECTIVE_DATE] <= DATE ( 2019331 ) ),
FILTER ( Coverage, Coverage[EXPIRE_DATE] > DATE ( 2019331 ) )
)

M2 Unearned Premium =
[M1 InForce Premium]
DIVIDE ( ( Coverage[EXPIRE_DATE] - DATE ( 2019331 ) )3650 )

1 ACCEPTED SOLUTION
Resolver II

If I interpret what you want that 2nd measure to do, it's to go through each policy and multiply by the proportion of a year that the policy has remaining, as of March 31. You then want to sum up all of these amounts across all policies - but only for a subset of policies that prior to March 31 and which had not expired by March 31.

Try this:

```M2 Unearned Premium =
SUMX (
FILTER (
Coverage,
AND (
Coverage[EFFECTIVE_DATE] <= DATE ( 2019, 3, 31 ),
Coverage[EXPIRE_DATE] > DATE ( 2019, 3, 31 )
)
),
Coverage[ANNUAL_PREMIUM]
* DIVIDE ( ( Coverage[EXPIRE_DATE] - DATE ( 2019, 3, 31 ) ), 365, 0 )
)```
3 REPLIES 3
Resolver II

If I interpret what you want that 2nd measure to do, it's to go through each policy and multiply by the proportion of a year that the policy has remaining, as of March 31. You then want to sum up all of these amounts across all policies - but only for a subset of policies that prior to March 31 and which had not expired by March 31.

Try this:

```M2 Unearned Premium =
SUMX (
FILTER (
Coverage,
AND (
Coverage[EFFECTIVE_DATE] <= DATE ( 2019, 3, 31 ),
Coverage[EXPIRE_DATE] > DATE ( 2019, 3, 31 )
)
),
Coverage[ANNUAL_PREMIUM]
* DIVIDE ( ( Coverage[EXPIRE_DATE] - DATE ( 2019, 3, 31 ) ), 365, 0 )
)```
Resolver II

Thank you, Peter,

That' great. I tried to add more criteria in the Filter but got a warning says Filter is limited to two criteria.

Any solutions?

Dennis

Coverage[TRANSACT] <> "CP",
Coverage[AUDIT] = "N"
Resolver II

Thank you, Peter, I used && and to got it. A good night.

## Helpful resources

Announcements

#### Launching new user group features

Learn how to create your own user groups today!

#### Check it Out!

Click here to read more about the November 2021 Updates!

#### Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors