topic Re: DAX SOLUTION NEEDED in DAX Commands and Tips

DAX SOLUTION NEEDED

bijaymaharjan — Wed, 31 Dec 2025 14:20:58 GMT

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

Re: DAX SOLUTION NEEDED

Ahmed-Elfeel — Wed, 31 Dec 2025 14:49:45 GMT

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.

Re: DAX SOLUTION NEEDED

bijaymaharjan — Wed, 31 Dec 2025 15:56:42 GMT

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)

Re: DAX SOLUTION NEEDED

Ahmed-Elfeel — Wed, 31 Dec 2025 18:34:54 GMT

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.

Re: DAX SOLUTION NEEDED

sarahlns — Thu, 01 Jan 2026 21:56:40 GMT

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

Re: DAX SOLUTION NEEDED

sarahlns — Thu, 01 Jan 2026 21:57:21 GMT

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

RETURN
SUMX ( ValidPolicies, [WrittenPremium] )

Re: DAX SOLUTION NEEDED

bijaymaharjan — Fri, 02 Jan 2026 16:12:40 GMT

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

Re: DAX SOLUTION NEEDED

FBergamaschi — Fri, 02 Jan 2026 16:16:50 GMT

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

Re: DAX SOLUTION NEEDED

maruthisp — Wed, 07 Jan 2026 01:46:47 GMT

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

Re: DAX SOLUTION NEEDED

v-lgarikapat — Wed, 07 Jan 2026 04:50:31 GMT

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.

Re: DAX SOLUTION NEEDED

v-lgarikapat — Wed, 14 Jan 2026 06:50:14 GMT

Hi bijaymaharjan ,

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

Best Regards,

Lakshmi.

Re: DAX SOLUTION NEEDED

bijaymaharjan — Thu, 22 Jan 2026 13:27:00 GMT

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.

Re: DAX SOLUTION NEEDED

v-lgarikapat — Fri, 23 Jan 2026 07:52:03 GMT

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

Thanks,

Lakshmi.