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
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.
While I think DAX can work for something like this, seems like Power Query is better tool for this job.
Here's your final output that will work when you add in new months and articles:
Much easier to see in PQ and the applied steps. Here is the excel file:
Firstly, click query editor-> Merge Queries as New like below:
You will achieve a table like this:
Then, after close&applied, you can create a calculate column using DAX below:
Unitary Cost = VAR previous_month = SWITCH ( Merge1[Month], "February", "January", "March", "February", "April", "March" ) RETURN IF ( Merge1[PURCHASES.Cost] <> BLANK (), Merge1[PURCHASES.Cost], CALCULATE ( MAX ( PURCHASES[Cost] ), FILTER ( PURCHASES, PURCHASES[Month] = previous_month && PURCHASES[Article] = Merge1[Article] ) ) )
Then create another calculate column:
Valorated Consumption = Merge1[Unitary Cost] * Merge1[Stock Consumption]
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
this solution is almost complete but in the case that a product "C" has cost values only for January and and April, for the month of March will not bring data because in Febroary there were no.
While I think DAX can work for something like this, seems like Power Query is better tool for this job.
Here's your final output that will work when you add in new months and articles:
Much easier to see in PQ and the applied steps. Here is the excel file:
Where does the February, Article B Unitary Cost of -3 come from? That field ( and a few others) doesnt exist. Is there some more logic there or is there missing data?
because product B does ot have values for February, so it maintains the value of January
User | Count |
---|---|
158 | |
109 | |
96 | |
84 | |
75 |
User | Count |
---|---|
154 | |
137 | |
131 | |
81 | |
62 |