Power BI Challenge 7 - Purchases, Inventory And Sales Entry from Amdi

Challenge Updates
1

Here’s Amdi’s entry for Power BI Challenge 7. @Amdi, feel free to add other details of your work.

image
image1443×814 70.7 KB

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.

To learn about the real-life scenario presented for the challenge, be sure to click on the image below.

power-bi-chal-1

2

Nice work here on your submission. You’ve really gone out there with your colors this time round so congrats on your creativity there.

Appreciate the write up as well looks like you set up the model another best practice way.

Also like how you’ve laid things out in grids and I think that all things combined it is starting to look more compelling than historic submissions.

I think maybe next time a good thing to challenge yourself on would be becoming more creative around the visualizations that you use. Maybe trying to think a little bit more outside the box around how to represent certain insights rather than just standard column charts. I think by challenging yourself with this you’ll extend your skill level that little bit more each time.

Sam