How to label max date and previous date (for all months) calculated column.

[mcinnisbr]

Hi, not sure if this can be done in calculated column.  or a measure.  Confused trying to figure it out.  

I have survey data with completion dates for multiple months. I want to label (in a calculated column) the max date and previous date for each month.  If it is not the max date, label as previous.

Name	Completion date	Maxdate
Canada	22/03/2021 4:31:41 PM	Max
Canada	21/03/2021 9:04:09 AM	Previous
USA	23/03/2021 9:05:48 AM	Max
USA	22/03/2021 12:07:39 PM	Previous
Canada	15/02/2021 4:31:41 PM	Max
Canada	14/02/2021 9:04:09 AM	Previous
USA	16/02/2021 9:05:48 AM	Max
USA	14/02/2021 12:07:39 PM	Previous

Essentially, i'm looking to represent my data (tied to those max/previous dates) as a clustered chart for those different monthly submissions.

Thanks if any help can be had. 

[Anonymous]

Hello @mcinnisbr,

Can you tell me if you built this graphic on Power BI??? If so, could you let me know how?

[ERD · Accepted solution]

Hello @mcinnisbr ,

You can try this code for your calculated column:

Maxdate = 
var currentName = 'Table'[Name]
var currentMonth = MONTH('Table'[CompletionDate])
var currentDate = 'Table'[CompletionDate]
var maxDate = 
CALCULATE(  
    MAX('Table'[CompletionDate]),
    FILTER('Table', 
        'Table'[Name] = currentName && 
        MONTH('Table'[CompletionDate]) = currentMonth)
   
)
return IF('Table'[CompletionDate] = maxDate, "max", "previous")

Did I answer your question? Mark my post as a solution!

[mcinnisbr]

I've attempted to scrap through this using abit of your suggestion and another.   It works.  But, it's not tagging the previous month (as you see with the Canada/usa example) with it's 'max' value.  It's just max for Canada for all months until it gets to the next name.

myMaxval =

VAR CurrentName ='table'[Name]

VAR _maxdate1 = MAXX(

FILTER(ALL('table'),'table'[Name] = CurrentName ),'table'[Completion time])

RETURN

IF('table'[Completion time]=_maxdate1,"max","Previous")

[Anonymous]

Column = VAR _maxdate = CALCULATE(MAX('Table'[Completion date]),ALLEXCEPT('Table','Table'[Name],'Table'[Completion date].[Year],'Table'[Completion date].[MonthNo])) RETURN IF('Table'[Completion date]=_maxdate,"max","Previous")

[mcinnisbr]

I understand this.  I'm getting an error (or the column measure error) when attempting to add the .[Year] and .[MonthNo].  I don't understand how to add this part?  Do i have to attach a date table column to this?