Forum Discussion

JustCraig's avatar
JustCraig
Frequent Visitor
1 year ago
Solved

Rankx: Finding the Last Transaction per Contract within a Date Period

Good day all,   I have a table with multiple banking transactions in per contract. A batch of transactions is run on a pecific date for multiple contracts. The client wants to see what all the last...
Need Help
JustCraig's avatar
JustCraig
Frequent Visitor
1 year ago

Ok, finally got it to work. No idea if this is the correct way, but it prodces the correct result:

StatusCount = 
VAR LatestTransactions = 
    FILTER(
        ALLSELECTED('MandatesData'),
        'MandatesData'[TransactionDate] = 
            CALCULATE(
                MAX('MandatesData'[TransactionDate]),
                ALLEXCEPT('MandatesData', 'MandatesData'[ContractNo], 'MandatesData'[MandateEffectiveDate])
            )
    )
VAR StatusSummary = 
    SUMMARIZE(
        LatestTransactions,
        'MandatesData'[Status],
        "ContractCount", DISTINCTCOUNT('MandatesData'[ContractNo])
    )
RETURN
IF(
    NOT ISBLANK([Last Transaction Date]),
    IF(
        HASONEVALUE('MandatesData'[Status]),
        SUMX(
            StatusSummary,
            IF(
                'MandatesData'[Status] = SELECTEDVALUE('MandatesData'[Status]),
                [ContractCount],
                0
            )
        ),
        SUMX(
            StatusSummary,
            [ContractCount]
        )
    ),
    BLANK()
)