Forum Discussion

fitconsulting's avatar
fitconsulting
Frequent Visitor
7 years ago
Solved

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 pur...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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 

      

    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:

     

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

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

v-yuta-msft's avatar
v-yuta-msft
Community Support
7 years ago

fitconsulting,

 

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.

 

fitconsulting's avatar
fitconsulting
Frequent Visitor
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

    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 

      

    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:

     

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

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