MAX value date field

[joep78]

Hi all,

I'm looking for a solution to get the MAX value of a date field but based on another value of another column. hereby a snapshot of the table with only the relavant column:

Syscode	Name	Contract end date	Contract code
19	Automatic renewal +1 year	maandag 31 juli 2028	66
18	Renewal +5 years	zaterdag 31 juli 2032	66
21	Renewal +5 years	vrijdag 14 september 2029	68
22	Automatic renewal +1 year	zondag 14 september 2025	68

I have a contract code (66 & 68) and both have to renewal options. 1 for 1 year, the other for 5 yrs. I'm only interested in the option with the highest contract end date. This count ofcourse per each contract. Does anybody have a solution for this. I was not able to figure this one out with MAX/FILTER or any other solution.

Thanks in advance for support.

[v-xuding-msft]

Hi @joep78 ,

If you use FILTER('Table','Table'[Contract end date] = MAX('Table'[Contract end date])) , the result is from the highest contract end date. How do you calculate it? Can you please share your formulas and show the expected results?  Then we will understand clearly and solve it quickly.

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi Joe - 

Is this what you are after (please see attached image). It is a matrix visual btw, and it has only the max date values you are after. It is a measure created and I used it in the matrix visual. If you confirm that is what you are after, I will be able to share the measure as well.

Regards

Eswar

[joep78]

Hi Eswar,

I think this is the solution i'm looking for with the only diference that for contract 68 the max year should be  Friday 14 september 2029. 

IS it possible to share the meassure so i'm able to test this. thanks in advance for your reply.

regards, Joep

[amitchandak]

Try like

calculate(count([Syscode]),filter(all(table),table[Contract end date]=max(table[Contract end date])))

prefer to join Contract end date with the date of date dimension and try

calculate(count([Syscode]),filter(all(date),date[date]=max(date[date])))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin