DAX SOLUTION NEEDED

I see a lot of bad DAX syntax and practices in the DAX you were given

-SUMMARIZE in which calculated columns are included in the SUMMARIZE call itself (nobody should ever do this)

-reference to columns without table reference included

-filtering tables when a few columns are enough

- SUMMARIZE to group a virtual table

...

As a human, I will give you DAX code written by myself that avoids pitfalls that AI is unable to detect and that guarantees you have a valid calculation all the time, it might need a few fixes but this is what a good (human) DAX developer should start from:

SolutionMeasure =
VAR MaxDate =
MAX( 'DateTable'[Date] )

VAR FilteredPol =
FILTER( ALL ( Policy[AccountingDate] ), Policy[AccountingDate] <= MaxDate )

VAR MaxTransPerPolicy =
ADDCOLUMNS(
SUMMARIZE(
ALL(Policy),
Policy[PolicyNumber],
Policy[Term]
),
"@MaxTrans", CALCULATE ( MAX( Policy[TransactionNum] ) )
)

VAR ValidPolicies =
FILTER(
ADDCOLUMNS(
MaxTransPerPolicy,
VAR CurrT = Policy[Term]
VAR CurrPolicy = Policy[PolicyNumber]
RETURN
"@TransType",
CALCULATE(
SELECTEDVALUE( Policy[TransactionType] ),
FilteredPol,
Policy[PolicyNumber] = CurrPolicy,
Policy[Term] = CurrT,
Policy[TransactionNum] = [@MaxTrans]
),
"@ExpDate",
CALCULATE(
MAX( Policy[PolicyExpDate] ), -- this might be subsituted by SELECTEDVALUE ( Policy[PolicyExpDate] ) as I am unsure on a few details
FilteredPol,
Policy[PolicyNumber] = CurrPolicy,
Policy[Term] = CurrT,
Policy[TransactionNum] = [@MaxTrans]
)
),
[@TransType] <> "CA" && [@ExpDate] >= MaxDate
)

RETURN
SUMX(
ValidPolicies,
Policy[WrittenPremium] ,
)

If this helped, please consider giving kudos and mark as a solution

me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Hi bijaymaharjan ,

Can you please check the below pbix file with solution:
DAX SOLUTION.pbix

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

Hi bijaymaharjan,

I hope you are doing well today ☺️❤️

Try this DAX mesure below it should work in case If your data always has transactions in chronological order (AccountingDate increasing with TransactionNum):

WrittenPremium_LatestActive :=
VAR MaxDate =
    CALCULATE ( MAX ( 'DateTable'[Date] ), ALLSELECTED ( 'DateTable' ) )

VAR FilteredPolicies =
    FILTER (
        ALLSELECTED ( Policy ),
        Policy[AccountingDate] <= MaxDate
    )

-- GET latest row based on AccountingDate!
VAR LatestRows =
    ADDCOLUMNS (
        SUMMARIZE ( FilteredPolicies, Policy[PolicyNumber], Policy[Term] ),
        "LatestAcc",
            CALCULATE (
                MAX ( Policy[AccountingDate] ),
                FilteredPolicies
            )
    )

RETURN
SUMX (
    LatestRows,
    VAR P  = [PolicyNumber]
    VAR T  = [Term]
    VAR LA = [LatestAcc]

    VAR TxnRow =
        CALCULATETABLE (
            Policy,
            FilteredPolicies,
            Policy[PolicyNumber] = P,
            Policy[Term] = T,
            Policy[AccountingDate] = LA
        )

    VAR TxnType = CALCULATE ( SELECTEDVALUE ( Policy[TransactionType] ), TxnRow )
    VAR ExpDate = CALCULATE ( MAX ( Policy[PolicyExpDate] ), TxnRow )
    VAR Prem    = CALCULATE ( SUM ( Policy[WrittenPremium] ), TxnRow )

    RETURN
        IF ( TxnType <> "CA" && ExpDate >= MaxDate, Prem, 0 )
)

If your case is different to AccountingDate increasing with TransactionNum then use this DAX Mesure (The-Safest-One):

WrittenPremium_LatestActive :=
VAR MaxDate =
    CALCULATE( MAX( 'DateTable'[Date] ), ALLSELECTED( 'DateTable' ) )

VAR MaxTxnPerPolicy =
    SUMMARIZE(
        FILTER( ALLSELECTED( Policy ), Policy[AccountingDate] <= MaxDate ),
        Policy[PolicyNumber],
        Policy[Term],
        "MaxTxn", MAX( Policy[TransactionNum] )
    )

RETURN
CALCULATE(
    SUM( Policy[WrittenPremium] ),
    -- keep only rows with AccountingDate up to the slicer MaxDate
    KEEPFILTERS( Policy[AccountingDate] <= MaxDate ),

    -- keep only the rows that match the max TransactionNum per Policy+Term
    KEEPFILTERS(
        TREATAS(
            MaxTxnPerPolicy,
            Policy[PolicyNumber],
            Policy[Term],
            Policy[TransactionNum]
        )
    ),

    -- exclude cancellations and expired policies
    Policy[TransactionType] <> "CA",
    Policy[PolicyExpDate] >= MaxDate
)

Written Premium (As Of) :=
VAR MaxDate =
MAX ( 'Date'[Date] )

-- Step 1: Policies visible as of slicer date
VAR PoliciesAsOf =
FILTER (
ALL ( Policy ),
Policy[AccountingDate] <= MaxDate
)

-- Step 2: Latest transaction per Policy + Term
VAR LatestTxn =
ADDCOLUMNS (
SUMMARIZE (
PoliciesAsOf,
Policy[Policy],
Policy[Term]
),
"MaxTxn",
CALCULATE (
MAX ( Policy[Transaction] ),
PoliciesAsOf
)
)

-- Step 3: Retrieve row-level attributes of latest transaction
VAR LatestRows =
NATURALINNERJOIN (
LatestTxn,
SELECTCOLUMNS (
PoliciesAsOf,
"Policy", Policy[Policy],
"Term", Policy[Term],
"Transaction", Policy[Transaction],
"TransactionType", Policy[TransactionType],
"ExpDate", Policy[PolicyExpDate],
"WrittenPremium", Policy[WrittenPremium]
)
)

-- Step 4: Apply business rules
VAR ValidPolicies =
FILTER (
LatestRows,
[TransactionType] <> "CA"
&& [ExpDate] >= MaxDate
)

RETURN
SUMX ( ValidPolicies, [WrittenPremium] )

Hi 
this should woek 

Written Premium (As Of) :=
VAR MaxDate =
MAX ( 'Date'[Date] )

-- Step 1: Policies visible as of slicer date
VAR PoliciesAsOf =
FILTER (
ALL ( Policy ),
Policy[AccountingDate] <= MaxDate
)

-- Step 2: Latest transaction per Policy + Term
VAR LatestTxn =
ADDCOLUMNS (
SUMMARIZE (
PoliciesAsOf,
Policy[Policy],
Policy[Term]
),
"MaxTxn",
CALCULATE (
MAX ( Policy[Transaction] ),
PoliciesAsOf
)
)

-- Step 3: Retrieve row-level attributes of latest transaction
VAR LatestRows =
NATURALINNERJOIN (
LatestTxn,
SELECTCOLUMNS (
PoliciesAsOf,
"Policy", Policy[Policy],
"Term", Policy[Term],
"Transaction", Policy[Transaction],
"TransactionType", Policy[TransactionType],
"ExpDate", Policy[PolicyExpDate],
"WrittenPremium", Policy[WrittenPremium]
)
)

-- Step 4: Apply business rules
VAR ValidPolicies =
FILTER (
LatestRows,
[TransactionType] <> "CA"
&& [ExpDate] >= MaxDate
)

RETURN
SUMX ( ValidPolicies, [WrittenPremium] )

Hi bijaymaharjan , thank you for reaching out to the Fabric Community.

FBergamaschi  maruthisp , sarahlns , Ahmed-Elfeel thank you for your prompt responses.

bijaymaharjan 

I wanted to follow up and confirm whether you’ve had the opportunity to review the information we shared. If you have any questions or need further clarification, please don’t hesitate to reach out.

We truly appreciate your engagement and thank you for being an active part of the community.

Best Regards,

Lakshmi.