Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
pravkan
Frequent Visitor

Dax to find Previous Gross Net Premium Amount

I'm looking for a Dax query that will allow me to capture Gross Net Premium Amount based on last contract reference number.

 

This what I have tried so 

 

Prev_GrossNetPremium = 
VAR CurrentContract = Premium[Capacity_Contract_Key]
VAR CurrentProvider = Premium[Capacity_Provider]
VAR CurrentPolicy = MAXX(Policy,Policy[LegacyReference])
VAR CurrentYear = Premium[AccountingYear]
VAR CurrentGrossNetPremium = [Gross Market Premium Original Currency] 
VAR RenewedFrom = LOOKUPVALUE(
    Policy[RenewedFrom],
    Policy[LegacyReference], CurrentPolicy
)
RETURN
CALCULATE(
    SUM(Premium[GrossNetPremium]),
    FILTER(
        Premium,
        Premium[Capacity_Provider] = CurrentProvider &&
        Premium[AccountingYear] = CurrentYear &&
        Premium[CurrencyType] = "Original Currency" &&
        Premium[Capacity_Contract_Key] <> CurrentContract 
    ),
    FILTER(
        Policy,
        Policy[RenewedFrom] = RenewedFrom
))

 

 

Problem with above query am getting nothing for the calculated field Prev_GrossNetPremium.

Am looking for dax query that will give me below result 

 

pravkan_0-1681379880509.png

 

Note : Column RenewedFrom contain the previous contract reference and is found in the Policy table and as for Premium Amount it is found in Premium table.

0 REPLIES 0

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.