Load Last Value

[DavidCov80]

Hello,

How can I load the last Cost Price from the attached files?

I need this to be loaded in the script as I want this as dimension.

So when I change the date in the calendar I will get different sales but last cost price should be always the same based on the latest date. So probably have to be grouped by Customer and Item as well?

Please help. 

[Frank_Hartmann]

Maybe like that:

1:
LOAD rowNo() as Row,
	Customer, 
     Name, 
     Item, 
     [Cost Price], 
     [Invoice date] ,
     [Sales]
FROM
[Data.xlsx]
(ooxml, embedded labels, table is Sheet1);

left join

2:
Load max(date([Invoice date])) as Max Resident 1;

Textbox:

=sum({<[Invoice date]={'=[Invoice date]=Max'}>}[Cost Price])

[DavidCov80]

Seems to be close, but it should show for all the lines (for the latest date). It only shows for one line. Not sure why. Please see the print screen. Any ideas? Thanks!

[Frank_Hartmann]

try this:
=max(Total{<[Invoice date]={'=[Invoice date]=Max'}>}[Cost Price])

[DavidCov80]

Thank you. Now it looks different. The idea is to get 7.2 for t the first three lines and then 6.6 for another four lines as on the picture: 

[Frank_Hartmann]

try this:

=Max(Total <Item> [Cost Price])

[DavidCov80]

Thanks. Seems to be working now. But the last problem is that when I select a date, it's only shows for that date. For example this line on 02/02/2019 has 1.8 Cost Price and Last Cost shows 1.8 as well, but it should be 2.2 like in other table no matter on date. That's why me idea was to get this value as dimension (in the Load Script) but not sure if this or other are possible? 

[NW1965]

This code in the load script works, a Max(TOTAL <Item. [Cost Price]) will only work if the cost price keeps going up.

/*
Load the original data table and create a key value on the customer and item field
*/
Data:
LOAD Customer
, Name
, Item
, [Cost Price]
, [Invoice date]
, Sales
, Customer & Item as KeyValue
FROM [C:\Users\43820653\Downloads\Data.xlsx]
(ooxml, embedded labels, table is Sheet1);

/*
Left join to the above table, using the key value, the maximum invoice date
grouped by customer and item
*/
LEFT JOIN(Data)
LOAD Customer & Item as KeyValue
, MAX([Invoice date]) as MaxInvDate
RESIDENT Data
GROUP BY Customer
, Item;


/*
Now reload the data table and generate the new key
the new key uses the max invoice date
Note that I have itemised the fields rather than use *
which alllows me to remove the KeyValue field defined above
*/
Data2:
NoConcatenate
LOAD Customer
, Name
, Item
, [Cost Price]
, [Invoice date]
, Sales
, Customer&Item&MaxInvDate AS Key
RESIDENT Data;

/*
Now get the cost price from the above table using the original invoice date
you will find that the only values that are joined are the ones that match max invoice date
cost price is loaded here as Max Cost price

Note that the resulting table will give you access to both Cost Price and Latest Cost Price
*/
LEFT JOIN(Data2)
LOAD Customer&Item&Num([Invoice date]) AS Key
, [Cost Price] as LatestCostPrice
RESIDENT Data2;

/*
Drop (remove) the original data table
*/
DROP TABLE Data;