cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
12Bowers12
Resolver II
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
Peter_R
Resolver II
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 )
)

View solution in original post

3 REPLIES 3
Peter_R
Resolver II
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 )
)

View solution in original post

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"

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

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.