Forum Discussion

m4kaveli's avatar
m4kaveli
Frequent Visitor
5 years ago

Display Value from Prior Line

Hi guys, I'm working with Power BI Desktop connecting to SQL and looking at insurance policy data. What i'm looking to do, is show expiring vs. new policy premium. The end result being that it will ...
Need Help
v-alq-msft's avatar
v-alq-msft
Community Support
5 years ago

Hi, m4kaveli 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

 

You may create measures as below.

Exp Date = MAX('Table'[ExpirationDate])
PolType = 
CONCATENATEX(
    DISTINCT('Table'[UniqCdPolicyLineType]),
    'Table'[UniqCdPolicyLineType],
)
Expiring Premium = 
var m =
CALCULATE(
    MAX('Table'[EstimatedPremium]),
    ALLEXCEPT('Table','Table'[UniqEntity])
) 
var x = 
CALCULATE(
    MAX('Table'[EstimatedPremium]),
    FILTER(
        ALLEXCEPT('Table','Table'[UniqEntity]),
        [EstimatedPremium]<m
    )
)
return
x
Renewal Premium = 
CALCULATE(
    MAX('Table'[EstimatedPremium]),
    ALLEXCEPT('Table','Table'[UniqEntity])
)

 

Result:

 

Or you may create a calculated table as below.

Result Table = 
SUMMARIZE(
    'Table',
    'Table'[UniqEntity],
    "Exp Date",
    MAX('Table'[ExpirationDate]),
    "PolType",
    CONCATENATEX(
        DISTINCT('Table'[UniqCdPolicyLineType]),
        'Table'[UniqCdPolicyLineType],
    ),
    "Expiring Premium",
    var m =
    CALCULATE(
        MAX('Table'[EstimatedPremium]),
        ALLEXCEPT('Table','Table'[UniqEntity])
    ) 
    var x = 
    CALCULATE(
        MAX('Table'[EstimatedPremium]),
        FILTER(
            ALLEXCEPT('Table','Table'[UniqEntity]),
            [EstimatedPremium]<m
        )
    )
    return
    x,
    "Renewal Premium",
    CALCULATE(
        MAX('Table'[EstimatedPremium]),
        ALLEXCEPT('Table','Table'[UniqEntity])
    )
)

 

Result:

 

Best Regards

Allan

 

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