Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I want to show stock on hand qty at any point of time.
I have 2 tables
1.StockBal
2.StockTransaction
StockBal table stores SOH of the item and for a site at the end of every month. So for a item and a for site it will 1 entry per month. StockBal - has Feb data only.
Stock Transaction table stores, daily transactions for a item and site.
In order for me to calculated the SOH for Jan. I subtract StockBal Qty with transacted qty to showing historical data. for March I add Stockbal Qty with Transacted qty to get SOH.
StkUnitQty is daily transcated qty
Measure to calculated Culmulated StkUnitQty
Measure to calculated SOH in Stock Transacted table is
SOH=
e.g StockBal for item 14L is
and Stock Transaction for same is
When I add this measure then it adds the SOH qty,
Requried result should be
It should show the next SOH qty rather then same adding the next qty in the previous qty.
I am sharing the pbix file
https://drive.google.com/file/d/1Z4I3hQgNFCxaIIQJByjkz4S6uj5PmJn8/view?usp=sharing
Thanks.
SOH is a caclulated column, not a measure. it can be simplified:
SOH = If(StockTransaction[AccountingDate]<=StockTransaction[DateFTStockBalance],
StockTransaction[FTStockBalance]-StockTransaction[Cumulative StkUnitQty]
,StockTransaction[FTStockBalance]+StockTransaction[Cumulative StkUnitQty])
Same for Cumulative StkUnitQty - it's a calculated column, not a measure. And that's probably ok since these values are not influenced by user filters.
I have to admit I don't fully understand why you sometimes add and sometimes subtract. Your calculated columns can be simplified - have a look at the documentation for CALCULATE(), and also consider replacing EARLIER with variables.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
78 | |
75 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |