Forum Discussion

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
4 years ago
Hi AbenaMina
Please confirm you have a date table. Are all other columns belong to the same table?

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

New Member

4 years ago

This one work, I made a little modification for the bolded part : TableName[CalendarDate] = LastDate

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]
        )
    )
RETURN
    IF ( MAX ( TableName[CalendarDate] ) = LastDate, LastDateValue )

tamerj1

Community Champion

4 years ago

AbenaMina
You are absolutely right. Also there is no need for calculate and ALLEXCEPT in 2nd variable

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