Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Combine rows from multiple columns into a new column

Hello everyone.

I've got these two tables:

  • FACT_STK
STK_IDSTK_ITM_IDSTK_DATESTK_QTYSTK_WHS
1I015424 Apr 2150WHS1
2I015426 Apr 21 30WHS1
4I015430 Apr 2120WHS1
5I01542 May 2115WHS1

 

  • FACT_MOV
MOV_IDMOV_ITM_IDMOV_DATEMOV_QTYMOV_CAUMOV_WHS
1I015425 Apr 2120SaleWHS1
2I01541 May 215SaleWHS1

 

As you can see from the stock quantities, 10 pieces of I0154 were sold in a day between 26 and 30 April, but no movements were registrated in the FACT_MOV.

In addition, 5 pieces of I0154 wew sold on May 1st, but the stock was updated the day after.

 

My aim is to calculate the average stock for each item, calculated as the sum of (stk_qty * days_of_stock) divided by (total days of stock)

 

so I thought that I could create a table with all the dates (from the minimum date of fact_mov and fact_stk to the maximum date) and create some columns like these:

 

ITM_IDDATESTK_QTYMOVEMENTCORRECT_STKWHS_ID
I015424 Apr 2150050WHS1
I015425 Apr 21502030WHS1
I015426 Apr 21 30030WHS1
I015427 Apr 2130030WHS1
I015428 Apr 2130030WHS1
I015429 Apr 2130030WHS1
I015430 Apr 21201020WHS1
I01541 May 2120515WHS1
I01542 May 2115015WHS1

 

How can I implement it in Power BI? Do you have a smarter idea to achieve my goal? I leave you also the pbix file: https://www.dropbox.com/s/m2z3g5xf19n7ji8/matteo%20-%20Copia.pbix?dl=0

 

Thanks.

Need Help
Tips and Tricks
Tutorial Requests

2 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you so much, it worked! Do you know how can I calculate the average stock, intended as sum of (CORRECT_STK* numbero of days of stock) divided by (total days of stock)

       

      In the example it would be (50*1 + 30*4 + 20*2 + 15*2) / 9 = 26,67 days

       

      Thank you!