Help with Grouping by account

[RyanS32229]

Hey,

Some context: We have a table with contracts, including the Account ID of the contract's account, and a Contract ID. Each Account can have multiple contracts in the table, but not at the same time, i.e. they can start a new contract if they close their old contract.

I want to calculate a sum of the amount of each account based on their MOST RECENT contract. We have a field for contract number, so effectively need to:

Group by Account ID, showing just the row for the highest contract number of that account.

I can then sum the contract amount to find the Amount we have in current contracts.

I have been trying this through GROUPBY and SUMMARIZECOLUMNS, but I cannot get either of these to work.

All help appreciated.

[v-yuezhe-msft]

@RyanS32229,

Please share sample data of your table and post expected result here so that we can provide you appropriate DAX.

Regards,
Lydia

[shebr]

Hi @RyanS32229

If you are able to add a flag on your contract table, 1 or 'Active' for current, 0 or 'Expired' for expired, you can then filter for only the Active contracts. You then should just be able to select both columns and see a sum of the contract value.

Does that help?

Thanks

shebr

[RyanS32229]

@shebr True!

So I'd need to make a calculated column. Something like

IF({Contract number = Max contract number for that Account ID}, 1, 0)

Any ideas on how I could design the expression?