cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NFI27 Regular Visitor
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 :

REFERENCELAST ENTRY DATESTOCK ENTRY QUANTITY COST 
A12/01/2017150 $                   1.00
B20/01/2017100 $                   5.00
B12/03/2017150 $                   4.50
A12/04/2017200 $                   1.10
A02/07/201750 $                   0.98
B12/07/2017120 $                   4.75
S25/07/2017150 $                   2.00
D23/08/2017200 $                   4.40
F27/08/2017150 $               145.00
F12/01/2018140 $               130.00
S20/01/201880 $                   1.80
D12/02/2018200 $                   4.15

 

 

 

And i want to get this :

REFERENCELAST ENTRY DATESTOCK ENTRY QUANTITY COST CALCULATED COLUM
A12/01/2017150 $                   1.00 
B20/01/2017100 $                   5.00 
B12/03/2017150 $                   4.50 
A12/04/2017200 $                   1.10 
A02/07/201750 $                   0.98 $                                                  0.98
B12/07/2017120 $                   4.75 $                                                  4.75
S25/07/2017150 $                   2.00 
D23/08/2017200 $                   4.40 
F27/08/2017150 $               145.00 
F12/01/2018140 $               130.00 $                                             130.00
S20/01/201880 $                   1.80 $                                                  1.80
D12/02/2018200 $                   4.15 $                                                  4.15

 

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

REFERENCECALCULATED COLUM
A $                                0.98
B $                                4.75
F $                            130.00
S $                                1.80
D $                                4.15

 

Thanks in advance.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
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.

 

Untitled.png

Highlighted
Ronald123 Member
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
Raimana Frequent Visitor
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

 

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 Regular Visitor
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:

REFERENCELAST ENTRY DATESTOCK ENTRY QUANTITY COST CALCULATED COLUM
A12/01/2017150 $                   1.00 
B20/01/2017100 $                   5.00 
B12/03/2017150 $                   4.50 
A12/04/2017200 $                   1.10 
A02/07/201750 $                   0.98 $                                                  0.98
B12/07/2017120 $                   4.75 $                                                  4.75
S25/07/2017150 $                   2.00 
D23/08/2017200 $                   4.40 
F27/08/2017150 $               145.00 
F12/01/2018140 $               130.00 $                                             130.00
S20/01/201880 $                   1.80 $                                                  1.80
D12/02/2018200 $                   4.15 $                                                  4.15

 

instead of showing me this:

 

REFERENCECALCULATED COLUM
A $                                0.98
B $                                4.75
F $                            130.00
S $                                1.80
D $                                4.15

 

It shows me this:

 

REFERENCELAST ENTRY DATESTOCK ENTRY QUANTITY COST 
A02/07/201750$3.08
B12/07/2017120$14.25
F12/01/2018140$275.00
S20/01/201880$3.80
D12/02/2018200$8.55

 

and i dont know how to fix this problem

Super User
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.

 

Untitled.png

Highlighted
Ronald123 Member
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

Raimana Frequent Visitor
Frequent Visitor

Re: Need a measure for last price (COST)

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

 

Untitled.png

NFI27 Regular Visitor
Regular Visitor

Re: Need a measure for last price (COST)

Worked perfectly. Thanks

Super User
Super User

Re: Need a measure for last price (COST)

You are welcome.