Merge Dates from multiple tables into one master date table

[rileybloke]

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

 

[MD610]

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.

 

[rileybloke]

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

 

[MD610]

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.

 

[rileybloke]

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?

 

[MD610]

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.

 

[rileybloke]

Thanks