Display Value from Prior Line

[m4kaveli]

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 only show the new policy term and find and display the expirting policy premium only on the same line....So, I want to search column A to see if it exists more than once. If so, I want to show the expiring and renewal esimtated premiums.

So in this example. I want to search to see if UniqEntity exists more than once. By default, I always want to display the latest renewing policy and then show the expiring data along with it...

Data Sample and expected results....So it would see that the UniqEntity of John exists 3 times in this range. It would take the most recent one using the expirationdate column and see that the policy expiring in 2/1/2021 is the most current and would look back in history for the same UniqCDPolicyLinetype and UniqEntity to compare the EstimatedPremiums of both as shown...

I was playing around with IF statements. IF PolType "PROP" exists prior "Renewing Expiration date or in this case 2/1/2021" date to look for the 2/1/2020 date and if found, then display the premium from that uniqline but can't figure this out. Any help would be appreciated! Using Direct Query.

Sorry this is hard for me to explain!

thanks in advance!

[v-alq-msft]

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]

@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]

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.

[amitchandak]

@m4kaveli , Join the date with date table and try to measures like

This Day = CALCULATE(sum('Table'[Estimated Premium]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))

Last Day Non Continuous = CALCULATE(sum('Table'[Estimated Premium]),filter(ALLSELECTED('Date'),'Date'[Date] =MAXX(FILTER(ALLSELECTED('Date'),'Date'[Date]<max('Date'[Date])),'Date'[Date])))

[m4kaveli]

Thanks for the quick response! I think i'm following...what needs to be in the date table?

[amitchandak]

@m4kaveli , all with your table to ignore context to get last week, will remove all other filters. Better to have separate table