Get the first value in a filtered column

[Hede92]

Hi all

I have two tables:

Sales

Item Number	Date	Sales Amount	Quantity	Code
A	01-01-2021	100	10	BRAVO
A	01-01-2021	200	20	CHARLIE
A	31-12-2020	300	30	ALPHA
B	01-01-2021	500	5	BRAVO
B	01-01-2021	400	4	CHARLIE

Items

Item Number	Description
A	Bike
B	Better Bike

I need a calculated column in my Items table which gives me the code of the highest sale in the last date possible.

So for item A the code would be CHARLIE. even though the 3rd row has a higher sale the date is older, so it's ruled out.

And for item B it would be BRAVO since it's the highest sale on the last possible date.

Any and all help is appreciated 🙂

[Ashish_Mathur]

Hi,

This should typically be solved via a measure in your visual.  Why do you want a calculated column formula in the input table?

[v-luwang-msft · Accepted solution]

Hi @Hede92 ，

Try the following steps:

Step1,use the following measure:

test =
VAR test1 =
    CALCULATE (
        MAX ( 'Sales'[Date] ),
        FILTER ( ALL ( 'Sales' ), 'Sales'[Item Number] = MAX ( 'Sales'[Item Number] ) )
    )
VAR test2 =
    CALCULATE (
        MAX ( 'Sales'[Sales Amount] ),
        FILTER (
            ALL ( 'Sales' ),
            'Sales'[Item Number] = MAX ( 'Sales'[Item Number] )
                && 'Sales'[Date] = test1
        )
    )
VAR test3 =
    CALCULATE (
        MAX ( 'Sales'[Code] ),
        FILTER ( ALL ( 'Sales' ), 'Sales'[Sales Amount] = test2 )
    )
RETURN
    test3

Step2， new column base on the measure:

codenew = [test]

Final you will see the below:

Wish it is helpful for you!

Best Regards

Lucien

[Jihwan_Kim]

Hi, @Hede92 

Please check the below picture and the sample pbix file's link down below.

The Highest Sales in the lastest date CC =
VAR currentitem = Items[Item Number]
VAR latestdate =
MAXX ( FILTER ( Sales, Sales[Item Number] = currentitem ), Sales[Date] )
VAR highestsales =
MAXX (
FILTER ( Sales, Sales[Item Number] = currentitem && Sales[Date] = latestdate ),
Sales[Sales Amount]
)
VAR Codename =
MAXX (
FILTER (
Sales,
Sales[Item Number] = currentitem
&& Sales[Date] = latestdate
&& Sales[Sales Amount] = highestsales
),
Sales[Code]
)
RETURN
Codename

https://www.dropbox.com/s/m61oilv89jw63uq/hede92.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[amitchandak]

@Hede92 , Create new columns like these in item table

max date = maxx(filter(sales, sales[item number] = item[item number]), sales[date])

max date sales = maxx(filter(sales, sales[item number] = item[item number] && sales[date] = item[max date]), sales[Sales Amount])

max date sales name = maxx(filter(sales, sales[item number] = item[item number] && sales[date] = item[max date] && sales[Sales Amount] = item[max date sales ] ), sales[Code])