Forum Discussion
VALORATED CONSUMPTION
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 |
While I think DAX can work for something like this, seems like Power Query is better tool for this job.
- Load your data tables, I named them as Purchases and Consumption
- Create two "Setup" Tables:
- There are two: AllMonths and AllArticles
- You can see the applied steps of each, but basically it appended both the data tables and some transformations to ensure we have all the available months and all the available articles
- We then did a full merge of the two setup tables. You will see that I added a custom column in each of the lookup tables of just "1". When doing a full merge based on that, we are really generating the cartesian product. Be sure to that months are sorted correctly (there was a column added for that)
- Then merged that table with the Consumption table to get the "Cost" column, which will generate some nulls ( and this is a good thing! )
- Next, group the rows by Article, and we want all the rows. This will produce a table for each Article with all the available months and cost. If you click in cell (not the actaul text) you will see
- Since the months are sorted correctly, we can fill down in each sub-table. So if there is a null it will pull down the previous value
- Then we can remove all the other columns, except the column with the new table. Expand that table
- Merge that table with the Consumption table to get the "Stock Consumption" figure. There will be nulls and that is ok
- Add a column to multiply those together
- Filter out the nulls and set data type.
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:
5 Replies
because product B does ot have values for February, so it maintains the value of January
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.
- Load your data tables, I named them as Purchases and Consumption
- Create two "Setup" Tables:
- There are two: AllMonths and AllArticles
- You can see the applied steps of each, but basically it appended both the data tables and some transformations to ensure we have all the available months and all the available articles
- We then did a full merge of the two setup tables. You will see that I added a custom column in each of the lookup tables of just "1". When doing a full merge based on that, we are really generating the cartesian product. Be sure to that months are sorted correctly (there was a column added for that)
- Then merged that table with the Consumption table to get the "Cost" column, which will generate some nulls ( and this is a good thing! )
- Next, group the rows by Article, and we want all the rows. This will produce a table for each Article with all the available months and cost. If you click in cell (not the actaul text) you will see
- Since the months are sorted correctly, we can fill down in each sub-table. So if there is a null it will pull down the previous value
- Then we can remove all the other columns, except the column with the new table. Expand that table
- Merge that table with the Consumption table to get the "Stock Consumption" figure. There will be nulls and that is ok
- Add a column to multiply those together
- Filter out the nulls and set data type.
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:
