@BrianJ I will give it a try. Hopefully I will be able to convince the usage of this software to management so they buy one for me
For tomorrow’s presentation, I am using my current challenge dashboard for a summarised view and details on the next page.
@BrianJ Looking forward to it! I really wanted it for my dot tooltip, instead of the three tables. I’m going to try and do it once I see your write up :).
Here is my entry for this challenge. Didn’t have much time to put this together, but put something together today that will suffice. Full writeup will be done in the next couple of days. Just added the Average Days calculations, so if you click on the web link right away it won’t look the same as the picture provided.
Thanks – I’ll check it out. The quality of some of the free stuff out there is pretty remarkable. I’ve got a video on Enterprise DNA TV coming out in the next couple of days on a program called Just Color Picker that is a phenomenal free tool for managing hex codes, that I’ve found to be a huge timesaver for report development.
My presentation consists of a report that reflects in the first instance in two hierarchical diagrams the time elapsed before the goods are received, and the time elapsed before invoicing the goods for purchase orders and materials.
Also, billing is analyzed compared to costs and profits for purchase orders and materials, as well as a comparative graph between received and invoiced tons of materials.
In the same way, five cards were placed, each one illustrating the profits, the contribution of the invoices to the profits, the inventory rotation, the quantities of purchase orders and invoiced material.
All of the above can be filtered by two segmenters of months and customers respectively.
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.
If you have two tables and the same Unique key in both tables. Want to move one column from table 1 to table 2 by merge. Use unique key to relate from table 2 to table 1.
The rows in table two have now expanded from 1.277 to 1.332 rows. I would expect it should be the same number of rows and just add the one column I asked for and placed data where there is a relation between the Unique key.
I have investigated and found that some records became double. There must be something I cannot see.
Question. Do anyone have an explanation to why it happens.
Must be getting old not to get a reference to a song! Cannot remember last time I saw a music video. They still making them? I used to watch them all the time when I was a kid on MTV.
Nice work on your entry! I agree that this was the most difficult challenge to date in terms of data modeling. You and I had a very similar approach in terms of building a single end-to-end fact table.
I always feel that entries present better when they are published to web, so that people can test them out hands-on. If you don’t have the ability to do this within your tenant, just let me know and I’m happy to publish it out for you and send you the link.
Brian
P.S. this offer stands for any other participant who doesn’t have publish to web capability. Just let me know if you need a link.