topic Finding Latest Values Based on 2 columns in the same table in DAX Commands and Tips

Finding Latest Values Based on 2 columns in the same table

AbenaMina — Fri, 13 May 2022 12:47:11 GMT

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

Re: Finding Latest Values Based on 2 columns in the same table

tamerj1 — Fri, 13 May 2022 15:52:42 GMT

Hi AbenaMina

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

Re: Finding Latest Values Based on 2 columns in the same table

AbenaMina — Fri, 13 May 2022 15:55:01 GMT

All the columns belong to the same table

Re: Finding Latest Values Based on 2 columns in the same table

tamerj1 — Fri, 13 May 2022 16:02:16 GMT

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

Re: Finding Latest Values Based on 2 columns in the same table

AbenaMina — Fri, 13 May 2022 16:38:33 GMT

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

Re: Finding Latest Values Based on 2 columns in the same table

tamerj1 — Fri, 13 May 2022 16:47:29 GMT

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

Re: Finding Latest Values Based on 2 columns in the same table

tamerj1 — Fri, 13 May 2022 20:43:26 GMT

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 )

Re: Finding Latest Values Based on 2 columns in the same table

AbenaMina — Fri, 13 May 2022 17:43:27 GMT

Re: Finding Latest Values Based on 2 columns in the same table

tamerj1 — Fri, 13 May 2022 17:44:53 GMT

Re: Finding Latest Values Based on 2 columns in the same table

AbenaMina — Fri, 13 May 2022 20:24:41 GMT

This one worked

Re: Finding Latest Values Based on 2 columns in the same table

AbenaMina — Fri, 13 May 2022 20:30:10 GMT

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 )

Re: Finding Latest Values Based on 2 columns in the same table

tamerj1 — Sat, 14 May 2022 04:31:11 GMT

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 )