Daily Changes from the available dates

[santoshlearner2]

Dear All,

I have daily stock company / product / stock quantity and price /on  a daily basis the value subtracts the value from the previous day by a calculated column, instead of a measure, the issue is when there the date is not available it does not give the daily change, for eg if the available dates are

29th May

1st June

2nd June

5th June

Then on 1st June since 31st may date is not available in the table it does not give the value, similarly on 5th June, since 4th June is not available it does not give the value.

wanted to compute daily stock changes and price changes, the highlighted cells refers to the respective Plus / Minus done.

Screenshot:

Data of the above : 

Date	Company	Product	Stock	Price	Stock Change	Price Change
05-06-2024	ABC	Book	100	10
05-06-2024	Microsoft	Book	50	20
05-06-2024	Ford	Book	60	30
05-06-2024	ABC	Pen	70	40
05-06-2024	Microsoft	Pen	74	50
05-06-2024	Ford	Pen	60	60
05-06-2024	ABC	Eraser	42	70
05-06-2024	Microsoft	Eraser	30	80
05-06-2024	Ford	Eraser	42	90
03-06-2024	ABC	Book	102	15	2	5
03-06-2024	Microsoft	Book	53	25	3	5
03-06-2024	Ford	Book	64	35	4	5
03-06-2024	ABC	Pen	74	65	4	25
03-06-2024	Microsoft	Pen	78	20	4	-30
03-06-2024	Ford	Pen	64	30	4	-30
03-06-2024	ABC	Eraser	46	70	4	0
03-06-2024	Microsoft	Eraser	34	30	4	-50
03-06-2024	Ford	Eraser	46	100	4	10
28-05-2024	ABC	Book	104	111	2	96
28-05-2024	Microsoft	Book	56	456	3	431
28-05-2024	Ford	Book	68	256	4	221
28-05-2024	ABC	Pen	78	146	4	81
28-05-2024	Microsoft	Pen	82	369	4	349
28-05-2024	Ford	Pen	68	136	4	106
28-05-2024	ABC	Eraser	50	136	4	66
28-05-2024	Microsoft	Eraser	38	1369	4	1339
28-05-2024	Ford	Eraser	50	1336	4	1236

How to do it .

Thanks in advance for your inputs.

Thank you

Santosh

[PowerBIDave · Accepted solution]

@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

[santoshlearner2]

Hi,

Superb of you, i am getting the values, but the issue is say if i am on 25th June , the value is 25.105 and 24th june the june the value is 25.000, then on 25th june i should get 0.105, but the value of 0.105 is showing for 24th june, it is not showing next to the 25th june date.  I think the previous day measure should be changed, can you help

[PowerBIDave]

@santoshlearner2

You just need to tweak the code I provided previously to get the changes going forward.

Something along the lines of the following.

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

Stock Change Going Forward =
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 __prevDate =
MAXX(
__filteredTable,
[Date]
)
VAR __result =
IF(
NOT ISBLANK(__prevDate),
__currentStockValue -
MINX(
FILTER(
__filteredTable,
[Date] = __prevDate
),
[Stock]
)
)
RETURN
__result

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

Price Change Going Forward =
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 __prevDate =
MAXX(
__filteredTable,
[Date]
)
VAR __result =
IF(
NOT ISBLANK(__prevDate),
__currentPriceValue -
MINX(
FILTER(
__filteredTable,
[Date] = __prevDate
),
[Price]
)
)
RETURN
__result

************************************

Hope that helps.

If this answers your question, please mark as a solution so others can find.

[lbendlin]

Do you need the results as a table or could it be a waterfall or ribbon chart?

[santoshlearner2]

Hi,

I wanted to put the same in a matrix showing as a trend, 

[lbendlin]

A matrix visual is not good for showing trends. A graph is easier to comprehend.

[Mahesh0016]

@santoshlearner2  Right-click on the visual field section and select "Show Items with No Data."( As mention in below screenshot).

Alternatively, create a date table and use it to plot the date column in a table visual. This will ensure you see continuous data.

@santoshlearner2  I hope this helps. Thank You!