Forum Discussion

bijaymaharjan's avatar
bijaymaharjan
Icon for Helper I rankHelper I
8 months ago
Solved

DAX SOLUTION NEEDED

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, Policy...
  • bijaymaharjan's avatar
    bijaymaharjan
    8 months ago

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

Ahmed-Elfeel's avatar
Ahmed-Elfeel
Icon for Super User rankSuper User
8 months ago

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's avatar
    bijaymaharjan
    Icon for Helper I rankHelper I
    8 months ago

    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's avatar
      Ahmed-Elfeel
      Icon for Super User rankSuper User
      8 months ago

      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's avatar
        bijaymaharjan
        Icon for Helper I rankHelper I
        8 months ago

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