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

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