Create Max Date Columns based on multiple columns (some with blanks)

[BA11501Banderso]

Hello, 

I am trying to create a column populated with the max date from a series of columns.  Some of the columns have blanks in them.  For example Notification "1" would have a date of 9/16/2022 and Notification "2" would have a date of 4/25/2022.

Notification          01                    02                  03                   04                   05                   21

1                    4/14/2022       4/27/2022                                9/16/2022

2                    4/16/2022                              4/25/2022         4/25/2022

3                    4/16/2022                                                                             12/31/2022

4                    4/14/2022

Thank you!

[v-yangliu-msft · Accepted solution]

Hi  @BA11501Banderso ,

Here are the steps you can follow：

1. Enter PowerQuery and copy the Table to form Table2.

2. Select all columns in Table2 except [Notification] - Transform - Unpivot Columns.

Result:

3. Create calculated column.

Table2:

max_date =
 MAXX(FILTER(ALL('Table2'),'Table2'[Notification]=EARLIER('Table2'[Notification])),[Value])

Table:

Max Date Columns =
MINX(FILTER(ALL('Table2'),'Table2'[Notification]=EARLIER('Table'[Notification])),[max_date])

4. Result:

Best Regards,

Liu Yang

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

[BA11501Banderso]

Thank you Liu!  You made it very easy to follow.

[FreemanZ]

DAX works on columns, so you would need to transpose your data and add a column with MAX function for each Notification.  

[Greg_Deckler]

@BA11501Banderso Might be best to unpivot your columns, or you can use this: Multi-Column Aggregations (MC Aggregations) - Microsoft Power BI Community