DAX SOLUTION NEEDED

[bijaymaharjan]

Hi all, 
Below is my requirement and I keep getting incorrect results. Hoping someone can help me out. 
I have 2 tables, Policy table has PolicyNumber, Term, TransactionNum, TransactionType, PolicyEffdate, PolicyExpDate, AccountingDate, State, Line of Business, WrittenPremium, Agent Name and the 2nd table is Date table. The date table has a direct relationship with Policy table through Accounting Date.

Layout in Power BI:
1. A Date slicer with Between feature where user can change the maximum date in the date slicer.
2. A Matrix that has Agent Name, Line of Business, and MC ID in the row and Written Premium in column. Should show the Grand Total of the Matrix.
3. Another Matrix that has State in Row and Written Premium in column. Should show the Grand Total of the matrix Requirements:

I need a DAX that does following:
1. Filter out the Policy Table based on the maximum date set in the Date slicer i.e. AccountingDate <= Maximum Date in the slicer
2. Find the maximum transaction for a MC_ID and Term on that filtered data.
3. Find the transaction type from the number 2 process.
4. Filter out the rows from 3 if the transaction type is "CA" or if the PolicyExpDate < Maximum Date in the slicer.
5. Then calculate the total written premium for the data remaining after process 4

Here is some examples:

Policy	Term	Transaction	EffDate	ExpDate	AccountingDate	State	Lob	Prem	Agent
AB1	1	1	9/1/2024	9/1/2025	1/1/2025	OH	Auto	500	CAB
AB1	1	2	9/1/2024	9/1/2025	4/12/2025	OH	Auto	100	CAB
AB1	1	3	9/1/2024	9/1/2025	7/15/2025	OH	Auto	200	CAB
AB2	1	1	4/15/2025	4/15/2026	4/15/2025	IN	Home	1000	ACT
AB2	1	1	4/15/2025	4/15/2026	8/22/2025	IN	Home	300	ACT
AB1	2	1	9/1/2025	9/1/2026	9/1/2025	OH	Auto	600	CAB
AB3	1	1	6/15/2025	6/15/2026	6/15/2025	IN	Auto	500	ACT

[v-lgarikapat]

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.

[v-lgarikapat]

Hi @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.

[bijaymaharjan]

@v-lgarikapat I apologize. I have been busy with other projects so have not been back to respond. I want to thank everyone for providing their answers. 

[v-lgarikapat]

@bijaymaharjan  You're welcome! Thank you for being such an active member of the community and for your contributions.

Thanks,

Lakshmi.

[maruthisp]

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

[FBergamaschi]

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

[sarahlns]

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

[sarahlns]

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

[Ahmed-Elfeel]

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
)

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

[bijaymaharjan]

Hi @Ahmed-Elfeel ,
Thank you for your response. I think we are getting really close but not fully. I am using the solution number 2. Currently, this code returns the premium from the latest transaction only. What I need is the sum of all the related transaction for a policy and term where the accounting date <= the maximum date in the slicer. In the example above, if i select the maximum date of the slicer to be 4/15/2025, then the total for ABC1 Term 1 should be 600 (500+100)

[Ahmed-Elfeel]

Hi @bijaymaharjan,

Thank you for the clarification her is corrected dax should work with your requirements (Give it a try and tell me if it works 🙂 :

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

-- only rows up to slicer max
VAR FilteredRows =
    FILTER( ALLSELECTED( Policy ), Policy[AccountingDate] <= MaxDate )

-- get max TransactionNum per Policy+Term (considering only FilteredRows)
VAR MaxTxnPerPolicy =
    SUMMARIZE(
        FilteredRows,
        Policy[PolicyNumber],
        Policy[Term],
        "MaxTxn", MAX( Policy[TransactionNum] )
    )

-- keep only policy+term where that max txn is not CA and its ExpDate >= MaxDate
VAR ValidPolicies =
    FILTER(
        ADDCOLUMNS(
            MaxTxnPerPolicy,
            "TxnType",
                CALCULATE(
                    SELECTEDVALUE( Policy[TransactionType] ),
                    FilteredRows,
                    Policy[PolicyNumber] = [PolicyNumber],
                    Policy[Term] = [Term],
                    Policy[TransactionNum] = [MaxTxn]
                ),
            "ExpDate",
                CALCULATE(
                    MAX( Policy[PolicyExpDate] ),
                    FilteredRows,
                    Policy[PolicyNumber] = [PolicyNumber],
                    Policy[Term] = [Term],
                    Policy[TransactionNum] = [MaxTxn]
                )
        ),
        [TxnType] <> "CA" && [ExpDate] >= MaxDate
    )

-- sum all WrittenPremium for the valid policy+term across all their rows up to MaxDate
RETURN
SUMX(
    ValidPolicies,
    VAR P = [PolicyNumber]
    VAR T = [Term]
    RETURN
        CALCULATE(
            SUM( Policy[WrittenPremium] ),
            FilteredRows,
            Policy[PolicyNumber] = P,
            Policy[Term] = T
        )
)

• This Measure should return 600 for your example 

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

[bijaymaharjan · Accepted solution]

Hi @Ahmed-Elfeel ,
I had to make a small tweak in your query to make it work. However, thank you for providing a solid base query for the result. Here is the final query:

another_try_v2 =
VAR _max_date =
CALCULATE(
MAX(
'DateTable'[Date]
),
ALLSELECTED(
'DateTable'
)
)

VAR _filterd_as_of_max =
FILTER(
--ALLSELECTED(
'Policy',
--),
'Policy'[AccountingDate] <= _max_date
)

VAR _max_trx_per_policy =
SUMMARIZE(
_filterd_as_of_max,
'Policy'[PolicyNumber],
'Policy'[Term],
"Max_seq", MAX(
'Policy'[TransactionNum]
)
)

VAR _valid_policies =
FILTER(
ADDCOLUMNS(
_max_trx_per_policy,
"TrxType",
VAR _PolicyNumber = [PolicyNumber]
VAR _term = [Term]
VAR _max_seq = [Max_seq]
RETURN
CALCULATE(
SELECTEDVALUE(
'Policy'[TransactionType]
),
_filterd_as_of_max,
'Policy'[PolicyNumber] = _PolicyNumber,
'Policy'[Term] = _term,
'Policy'[TransactionNum] = _max_seq
),
"ExpDate",
VAR _PolicyNumber_2 = [PolicyNumber]
VAR _term_2 = [Term]
VAR _max_seq_2 = [Max_seq]
RETURN
CALCULATE(
MAX(
'Policy'[PolicyExpDate]
),
_filterd_as_of_max,
'Policy'[PolicyNumber] = _PolicyNumber_2,
'Policy'[Term] = _term_2,
'Policy'[TransactionNum] = _max_seq_2
)
),
[TrxType] <> "CA" && [ExpDate] >= _max_date
)

RETURN
SUMX(
_valid_policies,
VAR _p = [PolicyNumber]
VAR _t = [Term]
RETURN
CALCULATE(
SUM(
'Policy'[WrittenPremium]
),
_filterd_as_of_max,
'Policy'[PolicyNumber] = _p,
'Policy'[Term] = _t
)
)