max if calculated column

[Anonymous]

Hi,

Want some help with creating a calculated column (not a measure). To explain I have recreated dummy data below. I have a column of data displaying the centre and a column displaying a submission month. What I'm looking for is a way to create the third column below. I want it to do is determine if the submission month is the latest submission (or most recent) by centre. So in the example below Perth has no Septmber 2017 submission so the latest submission for Perth is August 2017.

Centre	Submission Month	Submission
Sydney	May-17	Previous Submission
Brisbane	Jun-17	Previous Submission
Perth	Jul-17	Previous Submission
Darwin	Aug-17	Previous Submission
Melbourne	Sep-17	Previous Submission
Sydney	Jun-17	Previous Submission
Brisbane	Jun-17	Previous Submission
Perth	Jun-17	Previous Submission
Darwin	Jun-17	Previous Submission
Melbourne	Jun-17	Previous Submission
Sydney	Jul-17	Previous Submission
Brisbane	Jul-17	Previous Submission
Perth	Jul-17	Previous Submission
Darwin	Jul-17	Previous Submission
Melbourne	Jul-17	Previous Submission
Sydney	Aug-17	Previous Submission
Brisbane	Aug-17	Previous Submission
Perth	Aug-17	Latest Submission
Darwin	Aug-17	Previous Submission
Melbourne	Aug-17	Previous Submission
Sydney	Sep-17	Latest Submission
Brisbane	Sep-17	Latest Submission
Darwin	Sep-17	Latest Submission
Melbourne	Sep-17	Latest Submission

[Ashish_Mathur · Accepted solution]

Hi,

Try this calculated column formula

=if(CALCULATE(MAX([Submission Month]),FILTER(Data,Data[Centre]=EARLIER(Data[Centre])))=[Submission Month],"Latest submission","Previous month")

Please note that the answer in the 5th row will also be Latest submission becausee the date is Sep-17.

Hope this helps.

[Anonymous]

this is not working now..... I now have data for October 2017 in the and September 2017 is showing as the latest month and October is labelled as previous month

[Ashish_Mathur]

Hi,

Share the link from where i can download your file.  Also, point out the mistake in the result which you get by my formula.