Forum Discussion
Daily Changes from the available dates
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
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
7 Replies
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!Do you need the results as a table or could it be a waterfall or ribbon chart?
Hi,
I wanted to put the same in a matrix showing as a trend,
A matrix visual is not good for showing trends. A graph is easier to comprehend.
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
__resultHi,
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
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.
