Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Attached is a sample data coming from my ERP data source. I am having trouble identifying which date is my true master calendar date. Each date is needed as they are based on final calculations, so no dates can be removed/deleted.
A total of 5 tables with dates squared in red:
I have tried the join function, but with errors.
I have also added a 'Goal' sheet tab in the attached Excel for my end goal.
As always, thank you for the help!
If you fix a minor bug, I think it would work fine. The bug is that you're joining the Asset_Master before all of the transactional tables are added. Leases only have dates of the first of each month. And so you only have a description and status associated with the first of each month. So if you select a status, say, you will only get records that have a date of the first of a month. I believe it would be easily fixed by just waiting until all the transactional tables are added so that it joins to every row. Alternatively, just leave the Asset_Master as a separate table.
When you're joining rows rather than concatenating, you lose the ability to have a generic Type and Quantity, which can be useful in charts. But if you don't feel the need for those, you can do the joins like you have them, and add up the totals as you've done.
So, easily fixed bug, not how I'd have done it, but close, and should work fine. Thumbs up.
I would redo the data model like this:
Asset Master
Asset_ID
Description
Status
Transactions
Asset_ID
Date
Type
Quantity
Lease_Cost
Fuel_Amount
Depreciation_Amount
Maintenance_Cost
Calendar
Date
Month
So you leave your Asset Master table alone, and then you add Transactions table that is a concatenation of all the other data. For the leases, create an entry for every month a given lease is applicable (while loop, maybe). For Fuel, Depreciation, and Maintenance, just concatenate them in with the dates they have already.
For your chart, you have a couple options. Probably simplest is to use the Type and Quantity fields. Our types would be Leases, Depreciation, Fuel, and Maintenance. Our Quantity would be whatever quantity is associated with those. So just add Type as a column, and do a subtotal on it. But then we also have the specific quantity fields available so that we don't need to do set analysis to get something specific. So you could instead build your chart with multiple expressions instead of a Type dimension, and do a sum(Lease_Cost), sum(Fuel_Amount) and so on, and the total is just another column that adds them all up.
Is the expectation to rename every date field as 'Date' to tie with the master calendar?
The expectation is that you replace four of your tables (Leases, Fuel, Maintenance, and Depreciation) with a single Transactions table, and build the rows and set the Date field in the Transactions table as noted. I suppose you could think of it as renaming all your date fields to "Date", but it goes well beyond that. You won't get very far if you merely change them all to "Date" without changing the overall structure, and the Leases table wouldn't even load because it would have two fields named "Date".
John-see attached. What do you think of this model?
If you fix a minor bug, I think it would work fine. The bug is that you're joining the Asset_Master before all of the transactional tables are added. Leases only have dates of the first of each month. And so you only have a description and status associated with the first of each month. So if you select a status, say, you will only get records that have a date of the first of a month. I believe it would be easily fixed by just waiting until all the transactional tables are added so that it joins to every row. Alternatively, just leave the Asset_Master as a separate table.
When you're joining rows rather than concatenating, you lose the ability to have a generic Type and Quantity, which can be useful in charts. But if you don't feel the need for those, you can do the joins like you have them, and add up the totals as you've done.
So, easily fixed bug, not how I'd have done it, but close, and should work fine. Thumbs up.