Need a measure for last price (COST)

[NFI27]

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

Thanks in advance.

[Ashish_Mathur · Accepted solution]

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.

[Ronald123 · Accepted solution]

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

[Ashish_Mathur · Accepted solution]

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.

[NFI27]

Worked perfectly. Thanks

[Ashish_Mathur]

You are welcome.

[Raimana]

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

Add the calculated column :

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

Then add the measure :

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

It should do the trick

[NFI27]

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

instead of showing me this:

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

[Raimana]

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