Need Help on create the Measure

[Anonymous]

I am trying to create the Measure where the below table formation . Please help me how to create the Measure where it should count the recent transaction date based on ID as higher rank. Each Rank I want to calculate the total net amount , Paid Amount , Rejected Amount.  I attempted countrows with filter option & RankX but it showing error .

Transaction Date	ID	Service Code	Net Amount	Paid Amount	Rejected Amount	Manual mapping
7/7/2024	CLA0001	57250	250	100	150	1st Received transaction
8/8/2024	CLA0001	57250	150	150	0	2nd Recived Transaction
6/6/2024	CLA0005	88305	200	200	0	1st Received transaction
6/6/2024	CLA0005	99213	285	0	285	1st Received transaction
6/6/2024	CLA0005	71046	110	0	110	1st Received transaction
7/7/2024	CLA0005	99213	285	285	0	2nd time received
7/7/2024	CLA0005	71046	110	0	110	2nd time received
8/7/2024	CLA0005	71046	110	110	0	3rd time Received

[Anonymous · Accepted solution]

Hello,rajendraongole1 ,thanks for your concern about this issue.

Your answer is excellent! And I would like to share some additional solutions below.
Hi,@Anonymous .I am glad to help you.
If the calculated columns don't fit your needs, here is what I calculated by creating a measure, which enables dynamic transformations based on the data in the visual.
In fact I only modified the code rajendraongole1  provided by using RANK().
measure:

M_Rank = 
VAR _rank=
RANK(DENSE,FILTER(ALL(Rknk),'Rknk'[ID]=MAX('Rknk'[ID])),ORDERBY('Rknk'[Transaction Date],DESC),DEFAULT)
RETURN _rank

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian

[Anonymous]

Acutally as per @rajendraongole1 I have done the calculated column and the measure as well added . it was worked well. @Anonymous Thanks for your update regarding the measure. It's working well also. 

[rajendraongole1]

Hi @Anonymous - create a new calculated column to calcuate the rank the transactions for each id.

Rank:

TransactionRank =

RANKX(

    FILTER(

        'Rknk',

        'Rknk'[ID] = EARLIER('Rknk'[ID])

    ),

    'Rknk'[Transaction Date],

    ,

    DESC,

    DENSE

)

create measures to calculate the total Net Amount, Paid Amount, and Rejected Amount

TotalNetAmount =

CALCULATE(

    SUM('Rknk'[Net Amount]),

    FILTER(

        'Rknk',

        'Rknk'[TransactionRank] = 1

    )

)

total paid amount measure:

TotalPaidAmount =

CALCULATE(

    SUM('Rknk'[Paid Amount]),

    FILTER(

        'Rknk',

        'Rknk'[TransactionRank] = 1

    )

)

total rejected amount measure : 

TotalRejectedAmount =

CALCULATE(

    SUM('Rknk'[Rejected Amount]),

    FILTER(

        'Rknk',

        'Rknk'[TransactionRank] = 1

    )

)

Hope it helps, replace with your table name as per your model.

[Anonymous]

@rajendraongole1 Thanks for your reply. That means rank always should be calculated column . I have done same earlier in microsoft excel data model . Unfortunately I couldnt do as rank Measure for better performance. correct me if I am wrong, so Everytime I should create calculated column in the Model where the data range is changing( adding new data weekly once).