Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

max if calculated column

 

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.

 

CentreSubmission MonthSubmission
SydneyMay-17Previous Submission
BrisbaneJun-17Previous Submission
PerthJul-17Previous Submission
DarwinAug-17Previous Submission
MelbourneSep-17Previous Submission
SydneyJun-17Previous Submission
BrisbaneJun-17Previous Submission
PerthJun-17Previous Submission
DarwinJun-17Previous Submission
MelbourneJun-17Previous Submission
SydneyJul-17Previous Submission
BrisbaneJul-17Previous Submission
PerthJul-17Previous Submission
DarwinJul-17Previous Submission
MelbourneJul-17Previous Submission
SydneyAug-17Previous Submission
BrisbaneAug-17Previous Submission
PerthAug-17Latest Submission
DarwinAug-17Previous Submission
MelbourneAug-17Previous Submission
SydneySep-17Latest Submission
BrisbaneSep-17Latest Submission
DarwinSep-17Latest Submission
MelbourneSep-17Latest Submission
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    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.

3 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        Ashish_Mathur
        Super User
        8 years ago

        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.