topic Re: Create Max Date Columns based on multiple columns (some with blanks) in DAX Commands and Tips

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

BA11501Banderso — Wed, 09 Nov 2022 14:14:30 GMT

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!

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

Greg_Deckler — Wed, 09 Nov 2022 14:23:59 GMT

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

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

FreemanZ — Wed, 09 Nov 2022 14:27:31 GMT

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

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

Anonymous — Thu, 10 Nov 2022 05:12:22 GMT

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

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

BA11501Banderso — Thu, 10 Nov 2022 13:34:55 GMT

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