I am trying to get PowerBI to show the stock at a certian point in time.
I have managed to achieve this in SQL but when i put the SQL script into power it doesnt like the where clause on the date. However when i take this where clause out the SQL finds the stock from the last transaction date. So if i then want to know what the stock was on a certian date, but there has been transactions on a product after that date the stock for this product doesnt show as the MAX transactionID is after this date.
Please see SQL below
SELECT stocktransactions.partid, stocktransactions.physicalstockquantity, stocktransactions.referencedate FROM stocktransactions INNER JOIN(SELECT stocktransactions.partid, Max(stocktransactions.transactionid) AS MaxTransactionID FROM stocktransactions WHERE CAST(estocktransactions.referencedate AS DATE) <= ? GROUP BY stocktransactions.partid) groupedtt ON stocktransactions.partid = groupedtt.partid AND stocktransactions.transactionid = groupedtt.MaxTransactionID
The part that BI doesnt like is in bold and larger font.
Can anybody help me?
I want the following to happen.
If a product has a transaction after the date which at the moment i have as a slicer set as before, then i want it to show the maximum latest transaction before that date. However at the moment due to having to take out the BOLD line it just isnt showing as the max transaction date is after the date on the slicer
I tweaked it slightly to get the correct results, i am wondering if you know how to do one last tweak to get me the table displaying in the way i need.
As you can see from the table below i have 2 "STOCK" types
is there a way of getting the sum of all the maxtrans per STOCK type.
When i get rid of part number and just put STOCK as the main heading of the table it just shows the MAX transaction of that stock type before the date on the slicer, but what i need is the total of each product in the stock group and then each stock group added up to give you a stock group total.
Does this make sense, let me know if you need to know anything else