Forum Discussion

AbenaMina's avatar
AbenaMina
New Member
4 years ago
Solved

Finding Latest Values Based on 2 columns in the same table

Hi Team, I need help writing a DAX code that will give me the values in the Quantity Required field Basically, for every distinct Station_TruckNumber, I want to retrieve the Quantity of the Max C...
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi AbenaMina 

    Please confirm you have a date table. Are all other columns belong to the same table?

  • tamerj1's avatar
    tamerj1
    4 years ago

    AbenaMina 

    If you want to blank out the result of other rows then

     

    Quantity Required =
VAR LastDate =
    CALCULATE (
        MAX ( TableName[CalendarDate] ),
        ALLEXCEPT ( TableName, TableName[Station_TruckNumber] )
    )
VAR LastDateValue =
    CALCULATE (
        MAX ( TableName[Quantity] ),
        ALLEXCEPT (
            TableName,
            TableName[Station_TruckNumber] ),
            TableName[CalendarDate] = LastDate
    )
RETURN
    IF ( MAX ( TableName[CalendarDate] ) = LastDate, LastDateValue )

     

AbenaMina's avatar
AbenaMina
New Member
4 years ago

All the columns belong to the same table

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    AbenaMina 

    You have first to create a CalrndarYear colum. New Column >

    CalrndarYear = YEAR ( TableName[CalrndarDate] )

    then New Measure > 

    Quantity Required =
CALCULATE (
    MAX ( TableName[Quantity] ),
    ALLEXCEPT ( TableName, TableName[Station_TruckNumber], TableName[CalendarYear] )
)