The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
Hello all!
I have the following problem:
I have a table that contains, among other things, a column with item numbers, a column with the stock of the items, a column with the numbers of the products in which each item is included and a column with the number of items that are included in each product.
Now there is a second table that contains the number of products to be manufactured for the next 12 months. One column contains the product numbers and the other 12 columns, each of which represents a month, contain the order numbers for the corresponding product.
My goal is now to carry out a comparison and to have a value output as the result. Specifically, I would like to know up to which month production can be carried out with the current stock. To do this, I first need to determine from the first table how many products can be manufactured with the stock, i.e. the stock divided by the number of articles per corresponding product. Then the order numbers are to be subtracted from this number of producible products from the second table on a monthly basis until the result is 0. The link between the two tables must be the product numbers. The months up to the last month that can still be "served" with the stock are to be added up in order to be able to say how long production can continue according to the order situation. If it is >=12 months, I want the value 1 as the result. If it is >=6 months but <12 months, the result should be 2. And with <6 months the result is 3.
I hope it is understandable what I intend to do and anyone can give me a saving tip. Thank you very much for your help!
Solved! Go to Solution.
Hi @weberna ,
Please follow these steps:
1. Create relationship between the two tables:
2. Create a measure to calculate the result:
Measure =
VAR _a =
DIVIDE (
MAX ( 'Table1'[stock] ),
MAX ( 'Table1'[number of items per product] )
)
VAR _s1 =
MAX ( 'Table2'[M01] ) + MAX ( 'Table2'[M02] )
+ MAX ( 'Table2'[M03] )
+ MAX ( 'Table2'[M04] )
+ MAX ( 'Table2'[M05] )
+ MAX ( 'Table2'[M06] )
VAR _s2 =
MAX ( 'Table2'[M07] ) + MAX ( 'Table2'[M08] )
+ MAX ( 'Table2'[M09] )
+ MAX ( 'Table2'[M10] )
+ MAX ( 'Table2'[M11] )
+ MAX ( 'Table2'[M12] )
RETURN
IF ( _a <= _s1, 3, IF ( _a >= ( _s1 + _s2 ), 1, 2 ) )
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @weberna ,
Please follow these steps:
1. Create relationship between the two tables:
2. Create a measure to calculate the result:
Measure =
VAR _a =
DIVIDE (
MAX ( 'Table1'[stock] ),
MAX ( 'Table1'[number of items per product] )
)
VAR _s1 =
MAX ( 'Table2'[M01] ) + MAX ( 'Table2'[M02] )
+ MAX ( 'Table2'[M03] )
+ MAX ( 'Table2'[M04] )
+ MAX ( 'Table2'[M05] )
+ MAX ( 'Table2'[M06] )
VAR _s2 =
MAX ( 'Table2'[M07] ) + MAX ( 'Table2'[M08] )
+ MAX ( 'Table2'[M09] )
+ MAX ( 'Table2'[M10] )
+ MAX ( 'Table2'[M11] )
+ MAX ( 'Table2'[M12] )
RETURN
IF ( _a <= _s1, 3, IF ( _a >= ( _s1 + _s2 ), 1, 2 ) )
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@weberna , You should unpivot the second table. If possible convert month no to months.
Create a common Item/ Product and date table and join both tables together
https://radacad.com/pivot-and-unpivot-with-power-bi
Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.
User | Count |
---|---|
158 | |
106 | |
96 | |
83 | |
75 |
User | Count |
---|---|
154 | |
137 | |
131 | |
81 | |
62 |