Forum Discussion

12Bowers12's avatar
12Bowers12
Helper V
7 years ago
Solved

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

Need Help
  • Peter_R's avatar
    Peter_R
    7 years ago

    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

  • Peter_R's avatar
    Peter_R
    Resolver II
    7 years ago

    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's avatar
      12Bowers12
      Helper V
      7 years ago

      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's avatar
        12Bowers12
        Helper V
        7 years ago

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