Here’s Amdi’s entry for Power BI Challenge 7. @Amdi, feel free to add other details of your work.
Here is the link to the report:
And here’s how Amdi described it:
This was a challenge both on building a data model and for some of the measures.
The three fact tables to one inventory fact table.
My goal was to build one table out from three tables. In each of the tree tables there is a date column, which give the information on the day it happens for Purchase, Receiving and billing/invoicing. Unit cost shall be used in Billing table.
- Need a unique key. I chose “PO no | Mat no”
- To calculate the elapsed time for Purchase => Receiving – need the date for PO from purchase table to be placed in in receiving table.
- To calculate the elapsed time for Receiving=> Invoicing – need the date for receiving from receiving table to be placed in purchase table.
- In the billing table there is the unit price for sale. Calculate the profit - need the Unit Cost from purchase table to be placed in table for billing.
In Power Query I use Merge queries.
Ad. 1 In PQ. In all tables created the unique key “PO no | Mat no”.
Ad.2 In PQ took a copy of Purchase table and deleted all columns except Unique key and date for PO. By merges, placed the PO date in receiving tables.
Ad.3 2 In PQ took a copy of receiving table and deleted all columns except Unique key and date for receiving. By merge placed the receiving date in billing table.
Ad.4 In PQ took a copy of Purchase table and deleted all columns except Unique key and Unit Cost. By merges, placed the Unit cost in billing table.
In the Billing table deleted all records which did not received a Unit Cost from Purchase. Why it did not receive a Unit Cost could be, that the Purchase no. is before year 2020. But there was also few which has a young PO no. but it ist’n in the Purchase Table.
Now the three tables Purchase, Receiving, Billing is prepared to be appended in PQ and is named with Inventory Transaction.