Forum Discussion
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...
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 )
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
Community Champion
I misunderstood the requirement. My mistake.
please tryQuantity 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 ) ) - tamerj1
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 )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 variableQuantity Required = VAR LastDate = CALCULATE ( MAX ( TableName[CalendarDate] ), ALLEXCEPT ( TableName, TableName[Station_TruckNumber] ) ) VAR LastDateValue = MAX ( TableName[Quantity] ) RETURN IF ( MAX ( TableName[CalendarDate] ) = LastDate, LastDateValue )