cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
fitconsulting Frequent Visitor
Frequent Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Nick_M Senior Member
Senior Member

Re: VALORATED CONSUMPTION

While I think DAX can work for something like this, seems like Power Query is better tool for this job.  

 

  1. Load your data tables, I named them as Purchases and Consumption
  2. Create two "Setup" Tables:
    1. There are two: AllMonths and AllArticles
    2. 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
  3. 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)
  4. Then merged that table with the Consumption table to get the "Cost" column, which will generate some nulls ( and this is a good thing! )
  5. 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 

New Table with Nulls.png  

  1. 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
  2. Then we can remove all the other columns, except the column with the new table. Expand that table
  3. Merge that table with the Consumption table to get the "Stock Consumption" figure. There will be nulls and that is ok
  4. Add a column to multiply those together
  5. Filter out the nulls and set data type.  

Here's your final output that will work when you add in new months and articles:

Final Table.png

 

Much easier to see in PQ and the applied steps.  Here is the excel file:

https://1drv.ms/x/s!AoQIGRpzoxRHgbQqTnu90VJNbefNVw

5 REPLIES 5
Nick_M Senior Member
Senior Member

Re: VALORATED CONSUMPTION

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?  

fitconsulting Frequent Visitor
Frequent Visitor

Re: VALORATED CONSUMPTION

because product B does ot have values for February, so it maintains the value of January 

Community Support Team
Community Support Team

Re: VALORATED CONSUMPTION

@fitconsulting,

 

Firstly, click query editor-> Merge Queries as New like below:

1.PNG 

 

You will achieve a table like this:

2.PNG 

 

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]

Capture.PNG

 

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.

 

fitconsulting Frequent Visitor
Frequent Visitor

Re: VALORATED CONSUMPTION

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. 

Nick_M Senior Member
Senior Member

Re: VALORATED CONSUMPTION

While I think DAX can work for something like this, seems like Power Query is better tool for this job.  

 

  1. Load your data tables, I named them as Purchases and Consumption
  2. Create two "Setup" Tables:
    1. There are two: AllMonths and AllArticles
    2. 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
  3. 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)
  4. Then merged that table with the Consumption table to get the "Cost" column, which will generate some nulls ( and this is a good thing! )
  5. 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 

New Table with Nulls.png  

  1. 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
  2. Then we can remove all the other columns, except the column with the new table. Expand that table
  3. Merge that table with the Consumption table to get the "Stock Consumption" figure. There will be nulls and that is ok
  4. Add a column to multiply those together
  5. Filter out the nulls and set data type.  

Here's your final output that will work when you add in new months and articles:

Final Table.png

 

Much easier to see in PQ and the applied steps.  Here is the excel file:

https://1drv.ms/x/s!AoQIGRpzoxRHgbQqTnu90VJNbefNVw