Forum Discussion

RanHo's avatar
RanHo
Icon for Helper V rankHelper V
4 years ago

CALCULATING RENEWED POLICY BASED ON LAST YR PRODUCTION

  Good day Everyone,          I had a problem to solve here , I want to get the count/number of renewed policy for this yr based or filtered by the productions last year. I had a sample data here...
Need Help
Show and Tell
Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi RanHo ，

 

Please try measure as below.

TOTAL E-NO LAST YR =
CALCULATE (
    COUNT ( 'Table'[TRANSACTION NO] ),
    FILTER ( 'Table', YEAR ( 'Table'[ENTRY DATE] ) = YEAR ( TODAY () ) - 1 )
)
RENEWED =
VAR _LASTYEAR_LIST =
    CALCULATETABLE (
        VALUES ( 'Table'[E-NO] ),
        FILTER ( 'Table', YEAR ( 'Table'[ENTRY DATE] ) = YEAR ( TODAY () ) - 1 )
    )
RETURN
    CALCULATE (
        COUNT ( 'Table'[TRANSACTION NO] ),
        FILTER (
            'Table',
            YEAR ( 'Table'[ENTRY DATE] ) = YEAR ( TODAY () )
                && 'Table'[OLD E-NO] IN _LASTYEAR_LIST
        )
    )
PERCENTAGE = DIVIDE([RENEWED],[TOTAL E-NO LAST YR])

Result is as below.

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

RanHo's avatar
RanHo
Icon for Helper V rankHelper V
4 years ago

Anonymous thanks for reply, i will try this and give my feedback.