Forum Discussion

tracyhopaulson's avatar
tracyhopaulson
Resolver I
7 years ago
Solved

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

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?

 

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

10 Replies

    • tracyhopaulson's avatar
      tracyhopaulson
      Resolver I
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        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]))
         
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      tracyhopaulson
      Resolver I
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        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