cancel
Showing results for
Did you mean:
Regular Visitor

## Need a measure for last price (COST)

Hello All,

I was wondering if anyone could help me with this problem i am having. I have tried different ways to do it but came up short.

My problem is :

 REFERENCE LAST ENTRY DATE STOCK ENTRY QUANTITY COST A 12/01/2017 150 \$                   1.00 B 20/01/2017 100 \$                   5.00 B 12/03/2017 150 \$                   4.50 A 12/04/2017 200 \$                   1.10 A 02/07/2017 50 \$                   0.98 B 12/07/2017 120 \$                   4.75 S 25/07/2017 150 \$                   2.00 D 23/08/2017 200 \$                   4.40 F 27/08/2017 150 \$               145.00 F 12/01/2018 140 \$               130.00 S 20/01/2018 80 \$                   1.80 D 12/02/2018 200 \$                   4.15

And i want to get this :

 REFERENCE LAST ENTRY DATE STOCK ENTRY QUANTITY COST CALCULATED COLUM A 12/01/2017 150 \$                   1.00 B 20/01/2017 100 \$                   5.00 B 12/03/2017 150 \$                   4.50 A 12/04/2017 200 \$                   1.10 A 02/07/2017 50 \$                   0.98 \$                                                  0.98 B 12/07/2017 120 \$                   4.75 \$                                                  4.75 S 25/07/2017 150 \$                   2.00 D 23/08/2017 200 \$                   4.40 F 27/08/2017 150 \$               145.00 F 12/01/2018 140 \$               130.00 \$                                             130.00 S 20/01/2018 80 \$                   1.80 \$                                                  1.80 D 12/02/2018 200 \$                   4.15 \$                                                  4.15

So i can later make a table (visualizations) to show me this :

 REFERENCE CALCULATED COLUM A \$                                0.98 B \$                                4.75 F \$                            130.00 S \$                                1.80 D \$                                4.15

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User

## Re: Need a measure for last price (COST)

Hi,

This measure works

=if(HASONEVALUE(Table1[REFERENCE]),LOOKUPVALUE(Table1[COST],Table1[LAST ENTRY DATE],LASTNONBLANK(Table1[LAST ENTRY DATE],SUM(Table1[COST])),Table1[REFERENCE],VALUES(Table1[REFERENCE])),BLANK())

Hope this helps.

Member

## Re: Need a measure for last price (COST)

Hello @NFI27,

Try this calculed colum;

```MaxValue =
VAR MAXVALUE = CALCULATE(
MAX(Table1[LAST ENTRY DATE]);
FILTER(
Table1;
Table1[REFERENCE]=EARLIER(Table1[REFERENCE])))
RETURN
IF(Table1[LAST ENTRY DATE]=MAXVALUE; [ COST ])```

Greets,

Ronald

7 REPLIES 7
Frequent Visitor

## Re: Need a measure for last price (COST)

I have a similar problem https://community.powerbi.com/t5/Desktop/DAX-first-value-of-group/td-p/609670/jump-to/first-unread-m... but I think I can help with yours

```LATEST ENTRY DATE =
CALCULATE(
MAX('Table'[LAST ENTRY DATE]);
ALLEXCEPT('Table';'Table'[REFERENCE])
)```

```CALCULATED COLUMN =
CALCULATE(
SUM('Table'[ COST ]);
FILTER(
'Table';
'Table'[LAST ENTRY DATE]='Table'[LATEST ENTRY DATE]
)
)```

It should do the trick

Regular Visitor

## Re: Need a measure for last price (COST)

Hello Raimana,

I tried it but i have a problem. It shows me the value of all the enties together:

 REFERENCE LAST ENTRY DATE STOCK ENTRY QUANTITY COST CALCULATED COLUM A 12/01/2017 150 \$                   1.00 B 20/01/2017 100 \$                   5.00 B 12/03/2017 150 \$                   4.50 A 12/04/2017 200 \$                   1.10 A 02/07/2017 50 \$                   0.98 \$                                                  0.98 B 12/07/2017 120 \$                   4.75 \$                                                  4.75 S 25/07/2017 150 \$                   2.00 D 23/08/2017 200 \$                   4.40 F 27/08/2017 150 \$               145.00 F 12/01/2018 140 \$               130.00 \$                                             130.00 S 20/01/2018 80 \$                   1.80 \$                                                  1.80 D 12/02/2018 200 \$                   4.15 \$                                                  4.15

 REFERENCE CALCULATED COLUM A \$                                0.98 B \$                                4.75 F \$                            130.00 S \$                                1.80 D \$                                4.15

It shows me this:

 REFERENCE LAST ENTRY DATE STOCK ENTRY QUANTITY COST A 02/07/2017 50 \$3.08 B 12/07/2017 120 \$14.25 F 12/01/2018 140 \$275.00 S 20/01/2018 80 \$3.80 D 12/02/2018 200 \$8.55

and i dont know how to fix this problem

Super User

## Re: Need a measure for last price (COST)

Hi,

This measure works

=if(HASONEVALUE(Table1[REFERENCE]),LOOKUPVALUE(Table1[COST],Table1[LAST ENTRY DATE],LASTNONBLANK(Table1[LAST ENTRY DATE],SUM(Table1[COST])),Table1[REFERENCE],VALUES(Table1[REFERENCE])),BLANK())

Hope this helps.

Member

## Re: Need a measure for last price (COST)

Hello @NFI27,

Try this calculed colum;

```MaxValue =
VAR MAXVALUE = CALCULATE(
MAX(Table1[LAST ENTRY DATE]);
FILTER(
Table1;
Table1[REFERENCE]=EARLIER(Table1[REFERENCE])))
RETURN
IF(Table1[LAST ENTRY DATE]=MAXVALUE; [ COST ])```

Greets,

Ronald

Highlighted
Frequent Visitor

## Re: Need a measure for last price (COST)

You shouldn't add COST in the table but CALCULATED MEASURE.

Regular Visitor

## Re: Need a measure for last price (COST)

Worked perfectly. Thanks

Super User

You are welcome.