Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
fitconsulting
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
Anonymous
Not applicable

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

View solution in original post

5 REPLIES 5
v-yuta-msft
Community Support
Community Support

@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.

 

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. 

Anonymous
Not applicable

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

Anonymous
Not applicable

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 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

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.