Find the Minimum and Last Value based on multiple rows using DAX

[tracyhopaulson]

Hi, I need help to find the minimum of one set of values and last of another set of values.  I tried min/max formula but cannot figure out how to do min/last.  For ex) in order AB0057 I would like to pull 3/29 as my new minimum date from the Old Value, and 4/19 as my new date from the New Value (not 4/26 because it was created on 3/20 which is not the latest.  Can someone please help me?

[Anonymous]

For the latest date, a Calculated Column could look like this:

Latest Date =

var maxcreate = Calculate(

max(<YourTable>[CreateDate]),

ALLEXCEPT(<YourTable>,<YourTable>[Order])

)

return CALCULATE(

max(<YourTable>[NewValue]),

ALLEXCEPT(<YourTable>,<YourTable>[Order]),

<YourTable>[CreateDate] = maxcreate

)

Hope this helps,

Nathan

[tracyhopaulson]

Maybe I'm doing something wrong and didn't explain clearly but I do not get “4/19” result as the new date, instead I have 4/26 which is the max date from NewValue column, or 3/27 from CreateDate column. 

I need the date in last row of column #3 for each order group.  The example only show  1 order group.  Does this make sense?

[Anonymous · Accepted solution]

@tracyhopaulson - This following DAX Calculated Column is working for me. It first calculates the max CreateDate for the relevant Order. Then it finds the rows that have that Order and CreateDate and finds the max NewValue.

New Date = 
var maxcreate = Calculate(
	max('Table1'[CreateDate]),
	ALLEXCEPT('Table1','Table1'[Order])
)
return CALCULATE(
	max('Table1'[NewValue]),
	ALLEXCEPT('Table1','Table1'[Order]),
	'Table1'[CreateDate] = maxcreate
)

Result:

Hope this helps,

Nathan

[tracyhopaulson]

@Anonymous Thank you Nathan, your suggestion works for me.  You're a STAR! 

[Anonymous]

Hi,

Look at my solution. I think it answers the requirments.

[Anonymous]

Hi @tracyhopaulson 

There are many options.

Look at the following links

https://blogs.msdn.microsoft.com/lukaszp/2015/08/08/finding-the-latest-date-in-power-bi-desktop/

https://community.powerbi.com/t5/Desktop/DAX-expression-for-Max-Date-data-display-in-Table/td-p/1438...

Ta

[tracyhopaulson]

Actually, for each of the "Order" group, I want to return the last value from the column "NewValue", based on the latest "CreateDate".  Note: I'm not looking for the latest date in column NewValue. 

[Anonymous]

Hi,

Here you go, some additional help:

Min Date = CALCULATE(MIN(Table1[Created Day]), ALL(Table1))

Max Date = CALCULATE(MAX(Table1[Created Day]), ALL(Table1))

(MIN) Old Dates = CALCULATE(FILTERS(Table1[OldValue]),

FILTER(Table1, Table1[Created Day] = Table1[Min Date]))

(Latest) New Dates = CALCULATE(FILTERS(Table1[New Value]),

FILTER(Table1, Table1[Created Day] = Table1[Max Date]))

[tracyhopaulson]

Thank you and one more thing... my example shows only 1 order but there are several orders involved here too, so how do I get the last NewValue from each order group?

[Anonymous]

Go to Edit Queries and then you can use the group by functionality.

This will result with only N rows table, where N = number of orders in the table.

Then add the columns to the original table.

Do some DAX comparison calculations.

See attached pbix - https://we.tl/t-NPX0aTRlrd

Enjoy!