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.
I’m having trouble getting a query, could you please help me?
I have one first table with monthly stock consumption per product (CONSUMPTION TABLE).
I also have another table with monthly purchases as the following. In this case I got the last monthly purchase (PURCHASES TABLE).
Finally, I would need to get a table in which I have monthly products with their respective costs and consumption, and this is where I am having the most trouble (VALORATED CONSUMPTION)
CONSUMPTION | ||
Month | Article | Stock Consumption |
January | A | 10 |
February | B | 8 |
February | A | 30 |
February | B | 20 |
March | A | 40 |
March | B | 30 |
April | A | 25 |
April | B | 6 |
PURCHASES | ||
Month | Article | Cost |
January | A | -2 |
January | B | -3 |
February | A | -5 |
March | B | -8 |
April | A | -10 |
VALORATED CONSUMPTION TABLE | ||||
Month | Article | Cost | Unitary Cost | Valorated Consumption |
January | A | 10 | -2 | -20 |
February | B | 8 | -3 | -24 |
February | A | 30 | -5 | -150 |
February | B | 20 | -3 | -60 |
March | A | 40 | -5 | -200 |
March | B | 30 | -8 | -240 |
April | A | 25 | -10 | -250 |
April | B | 6 | -8 | -48 |
Solved! Go to Solution.
Hi @fitconsulting,
One sample for your refernce.
1. Create two calculated columns in your tables.
Monthno = SWITCH('CONSUMPTION'[Month],"January",1,"February",2,"March",3,"April",4)
MonthNo = SWITCH(PURCHASES[Month],"January",1,"February",2,"March",3,"April",4)
2. Create the new calculated columns in CONSUMPTION table as below.
Unitary Cost = VAR _same = CALCULATE ( SUM ( 'PURCHASES'[Cost] ), FILTER ( PURCHASES, PURCHASES[Month] = 'CONSUMPTION'[Month] && PURCHASES[Article] = 'CONSUMPTION'[Article] ) ) VAR _maxno = CALCULATE ( MAX ( PURCHASES[MonthNo] ), FILTER ( PURCHASES, 'PURCHASES'[Article] = CONSUMPTION[Article] && 'PURCHASES'[MonthNo] < 'CONSUMPTION'[Monthno] ) ) RETURN IF ( ISBLANK ( _same ), CALCULATE ( SUM ( PURCHASES[Cost] ), FILTER ( PURCHASES, PURCHASES[Article] = CONSUMPTION[Article] && 'PURCHASES'[MonthNo] = _maxno ) ), _same )
Valorated Consumption = CONSUMPTION[Unitary Cost]*CONSUMPTION[Stock Consumption]
Then we can get the result as we need. Please find the pbix as attached for your reference.
Regards,
Frank
Hi @fitconsulting,
One sample for your refernce.
1. Create two calculated columns in your tables.
Monthno = SWITCH('CONSUMPTION'[Month],"January",1,"February",2,"March",3,"April",4)
MonthNo = SWITCH(PURCHASES[Month],"January",1,"February",2,"March",3,"April",4)
2. Create the new calculated columns in CONSUMPTION table as below.
Unitary Cost = VAR _same = CALCULATE ( SUM ( 'PURCHASES'[Cost] ), FILTER ( PURCHASES, PURCHASES[Month] = 'CONSUMPTION'[Month] && PURCHASES[Article] = 'CONSUMPTION'[Article] ) ) VAR _maxno = CALCULATE ( MAX ( PURCHASES[MonthNo] ), FILTER ( PURCHASES, 'PURCHASES'[Article] = CONSUMPTION[Article] && 'PURCHASES'[MonthNo] < 'CONSUMPTION'[Monthno] ) ) RETURN IF ( ISBLANK ( _same ), CALCULATE ( SUM ( PURCHASES[Cost] ), FILTER ( PURCHASES, PURCHASES[Article] = CONSUMPTION[Article] && 'PURCHASES'[MonthNo] = _maxno ) ), _same )
Valorated Consumption = CONSUMPTION[Unitary Cost]*CONSUMPTION[Stock Consumption]
Then we can get the result as we need. Please find the pbix as attached for your reference.
Regards,
Frank
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |