Merge Dates from multiple tables into one master date table

rileybloke

New Member
Joined
Feb 7, 2013
Messages
43
Hi,

I am trying to extract dates from 2 different tables into a master date table...

I have 2 tables with dates in...

Order Table > Order Date
Delivery Table > Delivery Date

I also have a calendar table that has all dates from 01/02/2012 > 31/12/2020. The english requirement is...
If a date from the calendar table matches a date from the order table then give me the calendar date.
If a date from the calendar table matches a date from the delivery tablke then give me the calendar date.

I am creating an IF statement in a calculated column on the Calendar table...
=IF(VALUES(Calendar[Date])=VALUES(ORDER[DUE_DATE]),Calendar[Date],0)

I then create another calculated column to see if I have a date in the Delivery Table
=IF(VALUES(Calendar[Date])=VALUES(DELIVERY[DUE_DATE]),Calendar[Date],0)

But my IF statement above returns...
Calculation error in column 'Calendar'[]: A table of multiple values was supplied where a single value was expected.

I am wrapping up my DATES into VALUES but it its not working for me, should I use something else maybe?

Thanks :)
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

MD610

Board Regular
Joined
Feb 7, 2012
Messages
188
Could you elaborate on what your ultimate goal is for bringing the dates over into Calculated Columns in your date table?

There may be easier and more straightforward ways to accomplish you final objective without the Calculated Columns. For example, are you looking to count the orders per day as well as the deliveries per day?

One other note, it is usually a best practice to have your date table only span the ranges of dates available in your data. It can be less confusing in some cases and will make your pivots/date slicers much cleaner looking.
 
Upvote 0

rileybloke

New Member
Joined
Feb 7, 2013
Messages
43
Hi Mike D,

Thanks, I only want to show activity on the dates from my tables. Its not a financial based analysis, rather an movement of inventory. I prefer not have an entire calendar table as it would generate a lot of un-necessary dates.
If I can merge the dates from both tables into a single date table, and make that my date key table, then I wouldn't need to maintain a calendar table that just spanned my dates, it would be self maintained on the refresh.
Hope this clear it up a little.

Thanks
 
Upvote 0

MD610

Board Regular
Joined
Feb 7, 2012
Messages
188
That helps. However, for time intelligence funtions in DAX to work properly, you need a date table that starts from the min date in your data and goes to the last date in your data with every single date in between regardless if you have activities/data on those intermediate dates.

By default, if you relate both your data tables to the date table and then be sure to use fields only from your date table in rows and columns of your pivot, "unused" dates will automatically filter out based on your measures.

For example, if you drop the date field from your date table into rows, at first you will see every single date in your date table. However, if there is a relationship to your order table and you create a simple measure like this:

Orders:=COUNTROWS(Order Table)

Once that goes into your pivot, it will only have counts on the dates that have records in the Order table. This measure will return a blank for other dates that don't have orders. The default settings of a pivot "hides" the rows that are blank in your measure(s). So those dates without a value for [Orders] will disappear from your pivot once you add the measure.
 
Last edited:
Upvote 0

rileybloke

New Member
Joined
Feb 7, 2013
Messages
43
ADVERTISEMENT
Sorry I am working with rolling totals across the entire time dimension, so I will recieve a value for every record in my date table. Im still after a method of combining them. The order table has the majority of the dates I need, the delivery table has a few extra that dont appear in the order table, so I created this calculation on the delivert table to identify which dates did not appear in the orders table.
CALCULATE(COUNTROWS(ORDER),FILTER(ORDER,ORDER[DUE_DATE]=DELIVERY[DUE_DATE]))

No I need to bring those dates which are missing over into the order table?
 
Upvote 0

MD610

Board Regular
Joined
Feb 7, 2012
Messages
188
That can be fixed by a quick modification to your rolling total measure. Something like:

=IF(ISBLANK([Orders]) && ISBLANK([Deliveries]), BLANK(), [Rolling Total Measure])

Where [Orders] and [Deliveries] are measures counting the rows in their respective tables.
 
Upvote 0

Forum statistics

Threads
1,195,598
Messages
6,010,644
Members
441,558
Latest member
lambierules

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top