Measure based on another measure

[12Bowers12]

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 ( 2019, 3, 31 ) ),
    FILTER ( Coverage, Coverage[EXPIRE_DATE] > DATE ( 2019, 3, 31 ) )
)

M2 Unearned Premium =
[M1 InForce Premium]
    * DIVIDE ( ( Coverage[EXPIRE_DATE] - DATE ( 2019, 3, 31 ) ), 365, 0 )

[Peter_R · Accepted solution]

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 )
)

[12Bowers12]

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"

[12Bowers12]

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