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.

  • m4kaveli's avatar
    m4kaveli
    Frequent Visitor
    5 years ago

    v-alq-msft Hi Allan,

    Thank you for sending this. This did work, however, only on a small data set. Can anything be done to make it more scalable to larger data sets?

    • v-alq-msft's avatar
      v-alq-msft
      Community Support
      5 years ago

      Hi, m4kaveli 

       

      I am sorry for the late reply. You may try add a new step in Power Query with the following m codes. The pbix file is attached in the end.

      = Table.Group(#"Changed Type", {"UniqEntity", "UniqCdPolicyLineType"}, {{"Expiration date", each List.Max([ExpirationDate]), type nullable date},{"Expiring Premium", each List.Max(
List.Select( [EstimatedPremium],(x)=>x<>List.Max([EstimatedPremium]) )), type nullable number}, {"Renewal Premium", each List.Max([EstimatedPremium]), type nullable number} })

       

      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.