Retrieve max value per id

[Nuemio]

Hi there,

I'm running into a slight issue, I'm trying to return the maximum period that occurs for each company id. 
For example a companyid of 17001 should return a maximum period of 2305 and a company id of 41001 should return 2306. 

I'm trying to do this via a calculated column. So far using Max() returns the maximum period in the entire dataset

Any tips would be appreciated

[Dhairya]

Hey @Nuemio 

I tried to recreate your scenario I have taken the following table as input

Now, I have used follow DAX formula for creating a calculated column

Max period by company =
CALCULATE ( MAX ( TableA[Period] ), ALLEXCEPT ( TableA, TableA[CompanyID] ) )

I have got the following output, which will fulfill your requirements.

If it helps you then Please mark my solution as accepted so that others can find it quickly when they face a similar issue. Thank you!

[Nuemio]

Hi Dhairya, 

Thanks for getting back, however this proposed solution doesn't work and still returns the maximum period regardless of company id.

Attached is an export of the dataset:

https://docs.google.com/spreadsheets/d/1gbwXZ3SP0yG0bBv642C4IaNgsGGTEeHi/edit?usp=sharing&ouid=11725...

Thank you!

Nuemio

[Dhairya · Accepted solution]

Hey @Nuemio 
I download your dataset and tried the same DAX code I provided in the above reply, it worked perfectly

I tested by filtering two CompanyID "14001" and "47099" 

If you are still facing the issue then please share the DAX code you used for creating calculated column

[Nuemio]

Thanks Dhairya,

The issue I was getting was because I was using the FILTER() expression for a particular actuality code in the calculate which wasn't necessary. Your solution solved my problem!