Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
@RyanS32229,
Please share sample data of your table and post expected result here so that we can provide you appropriate DAX.
Regards,
Lydia
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
@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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |