Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
i am trying to create a report for my inventory movements, i would like to know the exact balance of inventory for each date that has Inventory move.
please see table below for clarification purposes (i am computing a PBI report off course):
the desired measure is the Yellow one, which shows the inventory balance after the movements of the same row (with respect to the remaining balance before this date).
Could you please help me with the desired DAX function and what i am missing?
Thanks a lot in advance
Hi @Dor-Y13
Thanks for the reply from @hackcrr , please allow me to provide another insight:
Here I create a measure, it's easily to understand that the outcome is the result of the cumulative values of Stock In minus Stock Out:
MEASURE =
VAR _currentDate =
MAX ( 'Table'[Base date] )
RETURN
CALCULATE (
SUM ( 'Table'[Stock In] ),
FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Base date] <= _currentDate )
)
- CALCULATE (
SUM ( 'Table'[Stock Out] ),
FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Base date] <= _currentDate )
)
The result is as follow:
Best Regards
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Dor-Y13
Based on the data provided in your image and the expected results of the yellow column metrics, I used the following SAMPLE example data:
I created a metric using the following DAX expression:
Stock aggregated balance =
VAR _table =
CALCULATETABLE (
SUMMARIZE (
'Table2',
'Table2'[Base date],
'Table2'[Stock In],
'Table2'[Stock out],
"_rolling1",
VAR _total =
CALCULATE (
SUM ( Table2[Stock In] ),
FILTER (
ALL ( 'Table2' ),
'Table2'[Base date] <= EARLIER ( Table2[Base date] )
)
)
RETURN
_total,
"_rolling2",
VAR _total =
CALCULATE (
SUM ( Table2[Stock out] ),
FILTER ( ALL ( Table2 ), 'Table2'[Base date] <= EARLIER ( Table2[Base date] ) )
)
RETURN
_total
)
)
VAR _rollingTotal_stock_In =
MAXX (
FILTER ( _table, 'Table2'[Base date] = SELECTEDVALUE ( Table2[Base date] ) ),
[_rolling1]
)
VAR _rollingTotal_stock_Out =
MAXX (
FILTER ( _table, 'Table2'[Base date] = SELECTEDVALUE ( Table2[Base date] ) ),
[_rolling2]
)
RETURN
_rollingTotal_stock_In - _rollingTotal_stock_Out
Using this metric in table gives you the expected result of your scalar yellow:
Best Regards,
hackcrr
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
96 | |
86 | |
78 | |
72 | |
67 |
User | Count |
---|---|
110 | |
104 | |
84 | |
65 | |
63 |