topic Re: How to label max date and previous date (for all months) calculated column. in DAX Commands and Tips

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

mcinnisbr — Thu, 25 Mar 2021 13:53:13 GMT

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.

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

Anonymous — Thu, 25 Mar 2021 14:21:00 GMT

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")

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

mcinnisbr — Thu, 25 Mar 2021 19:05:53 GMT

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?

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

mcinnisbr — Thu, 25 Mar 2021 20:10:29 GMT

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")

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

ERD — Sat, 27 Mar 2021 07:53:15 GMT

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!

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

Anonymous — Mon, 10 Jan 2022 17:14:57 GMT

Hello mcinnisbr,

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