Forum Discussion
Create Max Date Columns based on multiple columns (some with blanks)
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!
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
4 Replies
BA11501Banderso Might be best to unpivot your columns, or you can use this: Multi-Column Aggregations (MC Aggregations) - Microsoft Power BI Community
DAX works on columns, so you would need to transpose your data and add a column with MAX function for each Notification.
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
Thank you Liu! You made it very easy to follow.
