Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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.
User | Count |
---|---|
98 | |
90 | |
83 | |
70 | |
67 |
User | Count |
---|---|
115 | |
104 | |
101 | |
72 | |
64 |