Finding Latest Values Based on 2 columns in the same table

[AbenaMina]

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 CalendarDate

[tamerj1 · Accepted solution]

Hi @AbenaMina 

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

[AbenaMina]

[tamerj1]

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]

This one worked

[AbenaMina]

No, it didn't work
It doesn't work for the year field. I want for the date field, select the Quantity for any given maximum date for every distinct station_trucknumber.

Take a look at the screenshot,
I am highlighting distinct station_trucknumber, and its corresponding max calendardate so from these 2 combinations, I want the corresponding Quantity

[tamerj1 · Accepted solution]

@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]

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]

@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 )

[tamerj1]

@AbenaMina 

I misunderstood the requirement. My mistake. 
please try

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

[AbenaMina]

All the columns belong to the same table

[tamerj1]

@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] )
)

[tamerj1 · Accepted solution]

Hi @AbenaMina 

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