Calculate maximum amount as per date & map the respective amount as per primary key

[saurabh_kedia_]

Hi folks,

Need some help on this one!

I'm trying to map the amount against name by date preference between two tables.

For e.g. tables sale_data & Master_ID is connected on alias/Name (many to one relationship).

I need amount 2100 (as this is the amount aginst latest date) from table sale_data mapped to master_id table.

Moreover, the same should be available as column in data view.

Please feel free to reach out to me in case any more clarification is required.

Regards,

Saurabh Kedia

[v-cherch-msft]

Hi @saurabh_kedia_

You may add a rank measure first. Then you can get the latest amount as below:

Rank =
RANKX ( ALLEXCEPT ( Sale, Sale[Alias] ), CALCULATE ( MAX ( Sale[date] ) ) )

LatestAmount =
CALCULATE ( SUM ( Sale[amount] ), FILTER ( Sale, [Rank] = 1 ) )

Regards,

Cherie

[saurabh_kedia_]

Hi Cherch,

Thanks a lot for your inputs!

Actually, I knew this method.

I was to find a way to directly get the desired value.

Regards,

Saurabh 

[v-cherch-msft · Accepted solution]

Hi @saurabh_kedia_

You don't want to get the result by creating measures,right? Maybe you may try to use 'Group by' function in Query Editor and then use 'Merge Queries' to get the table as requested.

Regards,

Cherie

[saurabh_kedia_]

Hi Cherch,

Thanks for you inputs.

Regards,

Saurabh