Daily Changes from the available dates

santoshlearner2

Assuming your existing table just has columns named Date, Company, Product, Stock, and Price, you could create two calculated columns with the code below to produce the result you want. It may not be the most elegant code but it works. There are potentially better ways of doing this by modelling the data correctly and calculating the values with measures, but if you have only the one table of data and insist on using calculated columns then this should work. Good luck.

************ First Calculated Column ************

Stock Change =
VAR __currentDate = StockTable[Date]
VAR __currentCompany = StockTable[Company]
VAR __currentProduct = StockTable[Product]
VAR __currentStockValue = StockTable[Stock]
VAR __filteredTable =
FILTER(
StockTable,
StockTable[Company] = __currentCompany && StockTable[Product] = __currentProduct && StockTable[Date] > __currentDate
)
VAR __nextDate =
MINX(
__filteredTable,
[Date]
)
VAR __result =
IF(
NOT ISBLANK(__nextDate),
__currentStockValue -
MINX(
FILTER(
__filteredTable,
[Date] = __nextDate
),
[Stock]
)
)
RETURN
__result

************ Second Calculated Column ************

Price Change =
VAR __currentDate = StockTable[Date]
VAR __currentCompany = StockTable[Company]
VAR __currentProduct = StockTable[Product]
VAR __currentPriceValue = StockTable[Price]
VAR __filteredTable =
FILTER(
StockTable,
StockTable[Company] = __currentCompany && StockTable[Product] = __currentProduct && StockTable[Date] > __currentDate
)
VAR __nextDate =
MINX(
__filteredTable,
[Date]
)
VAR __result =
IF(
NOT ISBLANK(__nextDate),
__currentPriceValue -
MINX(
FILTER(
__filteredTable,
[Date] = __nextDate
),
[Price]
)
)
RETURN
__result