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

[JustCraig]

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 transactions per contract are and to see a summary table of all the last transaction statuses and the amount of contracts associated to each, based on a date slicer.

So if this is the sample data for instance:

MandateEffectiveDate	Status	ContractNo	TransactionDate	TransactionRank
12/30/2024	Payment successful	42299	12/30/2024 10:05:04 AM	1
12/30/2024	Payment successful	42299	12/30/2024 10:36:10 AM	2
12/30/2024	Payment successful	42299	12/30/2024 11:03:04 AM	3
12/30/2024	Payment successful	42299	12/30/2024 11:40:41 AM	4
12/30/2024	Transaction declined, please contact your bank for assistance.	42299	12/30/2024 2:18:29 PM	5
1/30/2025	Denied by Fraud System.	42299	1/30/2025 3:11:12 PM	6
1/30/2025	Denied by Fraud System.	42299	1/30/2025 3:12:31 PM	7
1/30/2025	Denied by Fraud System.	42299	1/30/2025 3:13:43 PM	8
1/30/2025	System malfunction	42299	1/30/2025 3:14:47 PM	9
1/30/2025	Invalid account number	42299	1/30/2025 3:27:30 PM	10
1/30/2025	Payment successful	42299	1/30/2025 7:09:15 AM	11
1/30/2025	Payment successful	42299	1/30/2025 7:09:49 AM	12
1/30/2025	Payment successful	42571	1/30/2025 7:32:56 AM	1
1/30/2025	System malfunction	42571	1/31/2025 10:02:11 AM	2
1/30/2025	System malfunction	42571	1/31/2025 6:02:03 AM	3
1/30/2025	Denied by Fraud System.	42571	1/31/2025 6:59:11 AM	4
1/30/2025	Denied by Fraud System.	42571	1/31/2025 7:01:38 AM	5
1/30/2025	Denied by Fraud System.	43253	1/31/2025 7:03:47 AM	1
1/30/2025	Payment successful	43253	1/31/2025 7:22:46 AM	2
1/30/2025	Transaction declined, please contact your bank for assistance.	43253	2/1/2025 10:31:37 AM	3
1/30/2025	System malfunction	43253	2/1/2025 11:07:53 AM	4
1/30/2025	Transaction declined, please contact your bank for assistance.	43253	2/1/2025 3:02:11 PM	5
1/30/2025	Transaction declined, please contact your bank for assistance.	43253	2/1/2025 3:07:15 PM	6
1/30/2025	System malfunction	43253	2/1/2025 6:02:07 AM	7
1/30/2025	Denied by Fraud System.	43253	2/2/2025 10:18:24 AM	8

Once the client selects lets say 1/30/2025 (which is the MandateEffectiveDate), the two visuals must display

Detail: Show only the last transaction per contract and all the relevant info, so:

MandateEffectiveDate	Status	ContractNo	TransactionDate	TransactionRank
1/30/2025	Payment successful	42299	1/30/2025 7:09:49 AM	12
1/30/2025	Denied by Fraud System.	42571	1/31/2025 7:01:38 AM	5
1/30/2025	Denied by Fraud System.	43253	2/2/2025 10:18:24 AM	8

And the other table must count the contracts within the table above and group the according the transaction Status:

Status	TotalContracts
Payment successful	1
Denied by Fraud System.	2

If Payment successful is selected in the second table it must filter the second table accordingly to only show that one contract.

So I added a calculated column to rank each transaction per contract:

TransactionRank = 
RANKX(
    FILTER(
        MandatesData,
        MandatesData[ContractNo] = EARLIER(MandatesData[ContractNo])
    ),
    MandatesData[TransactionDate],
    ,
    ASC,
    Dense
)

Then I created a measure to determine the last transaction - based on the dynamic date slicer:

MaxTransactionRankMeasure = 
CALCULATE(
    MAX(MandatesData[TransactionRank]),
    FILTER(
        ALLSELECTED(MandatesData),
        MandatesData[ContractNo] = MAX(MandatesData[ContractNo]) &&
        MandatesData[MandateEffectiveDate] <= MAX(MandatesData[MandateEffectiveDate])
    )
)

Then a flag measure to use as a filter on the first table:

IsLastTransactionMeasure = 
IF(
    MAX(MandatesData[TransactionRank]) = [MaxTransactionRankMeasure] &&
    NOT ISBLANK(MAX(MandatesData[TransactionRank])),
    1,
    0
)

And if I apply this as a filter to the first table, where IsLastTransactionMeasure = 1, it creates the desired result within the table after selecting the relevant date range.

But I now cannot get the second table visual to group the Transaction statuses and count the contracts associated. I tried this measure but it doesn't produce the desired relust and if you select a status the incorrect amount of contracts appears in the first table

TotalContracts = 
SUMX(
    VALUES(MandatesData[Status]),
    CALCULATE(
        DISTINCTCOUNT(MandatesData[ContractNo]),
        FILTER(
            ALLSELECTED(MandatesData),
            [IsLastTransactionMeasure] = 1
        )
    )
)

No idea if any of this makes any sense at this point

[JustCraig]

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

[parry2k · Accepted solution]

@JustCraig solution is attached, tweak it as you see fit. 

[JustCraig]

@parry2k thank you for your response and proposed solution.

The [Last Transaction Date] measure works well and after I applied it to a batch with relevant filters, it produced the correct detail as expected.

However, after applying the [Count by Status] measure, it only counts 1 instance it seems of each status, the result being:

When it should be as follows based on the detail: