Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi
I have already added the dax code for ending inventory in original post
Hi @hchauhan556 ,
Please try this:
Adjusted Inventory =
VAR CurrentMonth = SELECTEDVALUE('CurrentInventory'[Import Date])
VAR CurrentProductCode = SELECTEDVALUE('CurrentInventory'[Product Code])
VAR CurrentQty = SUM('CurrentInventory'[Quantity])
RETURN
IF(
CurrentQty = 0,
CALCULATE(
[Ending Inventory],
'CurrentInventory'[Product Code] = CurrentProductCode,
Month('CurrentInventory'[Import Date]) = Month(EOMONTH(CurrentMonth, -1))
),
CurrentQty
)
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi
the output from your code is wrong:
output is
Here is the sample of expected output:
as you can see from 24-01 the ending inventory was 105,960 and in 24-02 the current inventory was 0 so automatically the current inventory of 24-02 became 105,960 and performed other calculations.
the table which I have are
1. CurrentInvntory= important columns are highlighted in yellow in fig 1.1
Fig. 1.1
2nd table is named Demand which is the forecast table. Important columns are highlighted in yellow in fig 2.1
Fig. 2.1
3 table is InvoiceVolumeReport. Important columns are heightened in Fig. 3.1
Fig. 3.1
4 table is planned production. important fields are highlighted below in Fig. 4.1 and column value means QTY.
and I have created some tables with primary keys like tab;e name material id which has material code from all tables and table name date which has dates from all tables that can be used to create relations among them.
These all are tables of sample data
Hi @hchauhan556 ,
Please try this:
Adjusted Inventory =
VAR CurrentMonth =
SELECTEDVALUE ( 'CurrentInventory'[Import Date] )
VAR CurrentProductCode =
SELECTEDVALUE ( 'CurrentInventory'[Product Code] )
VAR CurrentQty =
SUM ( 'CurrentInventory'[Quantity] )
RETURN
IF (
CurrentQty = 0,
CALCULATE (
[Ending Inventory],
FILTER (
ALL ( 'CurrentInventory' ),
'CurrentInventory'[Product Code] = CurrentProductCode
&& MONTH ( MAX ( 'CurrentInventory'[Import Date] ) )
= MONTH ( EOMONTH ( 'CurrentInventory'[Import Date], +1 ) )
)
),
CurrentQty
)
Measure 2 = SUMX(VALUES('CurrentInventory'[Import Date]),[Adjusted Inventory])
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Neeko Tang
You have taken the ending inventory as a column but in my case, I need to find out the ending inventory also.
KR
Himanshu
Hi @hchauhan556 ,
There doesn't seem to be any mention in your original post of the logic required for [ENDING INVENTORY].
In order to solve your new problem , please create a new case and describe your logic and expected results in detail, and mark helpful answers to the problems described in the current case as solutions. Thanks in advance.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
as you can see in attached screenshot I have already added ending inventory code in main post
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
105 | |
98 | |
39 | |
30 |