Max Date across Multiple Columns in a Row and return Value + Column Name


#1

I have a table as displayed below, where I need a DAX Measure or two Custom columns that will give me the MAX Date and the Column Name it came from. I want to create a value that will give me the Max Date and Column name in the Row.

I can create a Custom Column to get the Max Date, but I need to also return the column name in another column.

List.Max({[Stage 1 Date],[Stage 2 Date],[Stage 3 Date],[Stage 4 Date],[Stage 5 Date],[Stage 6 Date],[Stage 7 Date]})


#2

Ok this is an interesting one because I wouldn’t solve this just with DAX.

I think you need to complete some work in the query editor first here.

You basically want to unpivot this table, or atleast these columns so that they move to just two columns.

One column will be Stage Number and the Other will be the results.

By doing this you can also get rid of all the null in this table.

Then your DAX measure will be very simple.

This is exactly how I would solve this quite quickly.

Let me know how you go.

Add the file here if no progress.

Chrs


#3

Hi Sam, I can unpivot the table no issues, but then I only want to keep one row for each and remove the rest from the Table, this is the challenge I have.

Any ideas

Thanks
Nev


#4

Hi Neville,

Sorry I’ve read you comment a few times and not really understanding it too well.

When you unpivot, you should have two columns of information. One for the stage and then one for the date.

From here you have many options to run calculations using DAX measures. This is what I would provide.

What sort of calculations are you after? For example what context are you using in your tables or visuals.

Once a table is setup in an unpivoted way I’m confident finding the max date for anything should be super easy.

Would just be as simple as writing MAX( Date Column ) regardless of the context of the calculation actually.

Chrs
Sam


#5

Hi Sam, I know I could filter the DAX measures based on MAX date, but I would have to do this for every DAX calculation, as I only want to keep the row in the table with the MAX date as I’m not interested in any of the other rows as all the columns are exactly the same.

Hope this is clear now.

Thanks
Nev


#6

Hi Nev,

Sorry I’m just not getting the naunces to this scenario.

I’m probably going to have to see more with either a file, or more detailed images of what you’re looking at.

In my mind, I’m imagining this is quite simple, but it doesn’t seem that way as we’re not getting what you need.

Chrs


#7

Hi Sam,

There are 20 columns in the table and I need the Column Name and value of the MAX date to be represent in a two columns, 1 Column called “Stage Name” and the Other with “Stage Date”. If I unpivot the columns named Stage 1 Date to Stage 7 Date, I will end up with multiple rows containing the other 13 colums where there was multiple dates is the columns Stage 1 to Stage 7 Date. I only require one row.

In the example table I provided, if I unpivot, I will end up with 3 rows.

Row #; Stage Name; Stage Date; Other Columns in Table.
9;Stage 3 Date;03/10/2016; Other Columns from same Row in Table
9;Stage 4 Date;28/06/2016; Other Columns from same Row in Table
9;Stage 5 Date;19/10/2016; Other Columns from same Row in Table

In the above example, I only wish two Keep the first Row as that’s the one with the MAX date and I also have the Stage name (Stage 3) that contained the Max Date.

In the other columns I have as Total Value for that row, so if I end up with three rows, then the total value will be tripled. I want to avoid have to filter all the rows in the DAX Measures just to include the one row with the MAX date, which is why I just was to delete all the other rows from the Table.

Maybe this is not as simple as I thought it would be?

Thanks
Nev