Forum Discussion
Calculating with constant values
Hello everyone.
I have a Stock Table with constant available items QTY's.
And I also have a table with Production QTY's.
I want to create a Matrix Visual that shows how much we have in Stock after each Production Day, as below:
Item A has 2500 unts in Stock Table.
| Date | 01/01/2021 | 02/02/2021 | 03/03/2021 | |||
| Item | Production QTY | Stock Left | Production QTY | Stock Left | Production QTY | Stock Left |
| A | 500 | 2000 | 300 | 1700 | 450 | 1350 |
Could you guys give me a hint on how to do it? I'm still learning PBi and got stuck with this problem.
Hi, Anonymous
To create measures like this:
_Production Qty = VAR _currentDate = MAX ( 'Production Table'[Production Date] ) VAR _Qty = CALCULATE ( SUM ( 'Production Table'[Prodution Qty] ), FILTER ( ALL ( 'Production Table' ), 'Production Table'[Item] = MAX ( 'Stock Table'[Item] ) && 'Production Table'[Production Date] = _currentDate ) ) VAR _isBlank = IF ( ISBLANK ( _Qty ), 0, _Qty ) RETURN _isBlankStock left = SUM ( 'Stock Table'[In Stock] ) - CALCULATE ( SUM ( [Prodution Qty] ), FILTER ( ALL ( 'Production Table' ), [Item] = MAX ( 'Stock Table'[Item] ) && [Production Date] <= MAX ( [Production Date] ) ) )Result:
Please refer to the attachment below for details. Hope this helps.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
- AllisonKennedy
Community Champion
Anonymous Can you please provide sample of the two tables - what does the data look like? DO you need to take the stock level for the latest date only or add all dates from the stock table? Do you have a Product table? Give us a clue of what your raw data looks like and we can help better.
Thank you so much for replying.
And I'm so sorry. I was in a rush and that made me forget about adding details.
Anyways, my datas look like this:
(Titles in first row only for identification in this thread)- Stock Table:
- Production Table:
Basically, I want to deduct the amount of Production Qty from the Stock and show at the Visual.
Something just like this in a Matrix:
I'm struggling with it because I can't make the Production Qty to deduct from the last available Stock.
- AllisonKennedy
Anonymous Does the Stock Left value change each month? year?
v-angzheng-msft Has provided a solution, but just curious what time range you need this to work for- how do you know not to subtract the Production Qty for the 1st of the month for example?
- v-angzheng-msft
Community Support
Hi, Anonymous
To create measures like this:
_Production Qty = VAR _currentDate = MAX ( 'Production Table'[Production Date] ) VAR _Qty = CALCULATE ( SUM ( 'Production Table'[Prodution Qty] ), FILTER ( ALL ( 'Production Table' ), 'Production Table'[Item] = MAX ( 'Stock Table'[Item] ) && 'Production Table'[Production Date] = _currentDate ) ) VAR _isBlank = IF ( ISBLANK ( _Qty ), 0, _Qty ) RETURN _isBlankStock left = SUM ( 'Stock Table'[In Stock] ) - CALCULATE ( SUM ( [Prodution Qty] ), FILTER ( ALL ( 'Production Table' ), [Item] = MAX ( 'Stock Table'[Item] ) && [Production Date] <= MAX ( [Production Date] ) ) )Result:
Please refer to the attachment below for details. Hope this helps.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
