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
Community ChampionIf 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 )