Forum Discussion
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 CalendarDate
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 )
11 Replies
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 )
All the columns belong to the same table
- tamerj1
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
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 worked
