Compare months in two different tables (and see if there is a match)

[Anonymous]

Hello Community! 

I have a date table, and two tables related to that:   Orders table   &  Shipping table.     Orders is related thru Due Date column.   Shipping table is related thru Date Shipped column.  

I would like to have the formula output a true/false.    "True" if the Date Shipped month matches the Due Date month, and "False" if it does not.   

I will put the result in a table visual and then filter on True/False so I can see which shipments actually shipped in the month they were supposed to.  

I was trying to use CONTAINS but that is not working.    Again, both tables are related thru the date table.   

Any suggestions are appreciated.

[az38]

@Anonymous 

Hi, I didn't check but try a column

Column = (
FORMAT('Shipping table'[Date Shipped]), "YYYYMM") = FORMAT('Orders table'[Due Date]), "YYYYMM")
)

But make sure you have relationships between tables

[Anonymous]

Hi @az38   I will give that a try shortly.  

Just to confirm:   I do not have a direct relationship between the tables.     I do have each table related back to my main date table.  

Can that date table, in this scenario, be used sort of as a "bridge table" between the Orders and the Shipped tables?  

[az38]

@Anonymous 

good question. do you have some shipping ID in data model?

[Anonymous]

@az38   For what it's worth, I am trying to accomplish the exact same thing, but for the Order ID.    In other words, match results from the Orders table to the results on the Shipping table, using  Order ID as the variable.   I tried using the new indirect relationship between the "orders" columns.....but it is throwing an error message.  

This is the same thing I am trying to do in my original post....essentially see if values from one table match those in another table.  

[Anonymous]

Yes I have an "Order ID" table.     It just contains the Order ID   (of each individual purchase order).     It is being used as a dim table.  

It is related  (one to many) to both the Orders table  and  the  Shipping table    (but they are not directly related as it would introduce ambiguity between other tables).  

I just created a many-to-many  inactive relationship between them.   Do you think I could use the USERELATIONSHIP function perhaps?    

[az38]

@Anonymous 

if you have data model like 

Orders Table *-1 Orders ID 1-* Shipping table

my soution should work correct

[Anonymous]

Here is an image of the data model

Again, no direct relationships exist between the Orders table and the Shipping table.    I'm not able to create direct relationships due to other direct connections that already exist.  

[az38]

OK @Anonymous 

as I see in your data model each order could have a few rows in shipping table and the same for orders table.

Could one order has different shipping dates? due dates?

If so, which exactly dates you need to compare? (first, last or any other, what business logic do you need for this case?)

Maybe you need column like

Column = 
var _shippingDate = CALCULATE(MIN('Shipping table'[Date Shipped]))
var _ordersDate = CALCULATE(MIN('Orders table'[Due Date]))
RETURN
FORMAT(_shippingDate, "YYYYMM") = FORMAT(_ordersDate, "YYYYMM")

[Anonymous]

I added that last formula to the shipping table, and it worked   (in terms of matching shipped order dates to due dates).   🙂

That's nice....but a little too specific.    What I really need is this same comparison, but just at the month level.    

The business logic for this is that almost always the ship date will not exactly match the due date.    But they generally will be in the same month...and this is what I am trying to determine/match. 

Is there a way to just look at the month level...as opposed to the specific date? 

[az38]

@Anonymous 

statement 

FORMAT(_shippingDate, "YYYYMM")

compares dates by Year and Month.

for example:

order due date is 1st January 2020 will be compared as "202001"

shipping date 15 Jan 2020 will be presented as "202001"

So, it's completely the same and it should satisfy your expectation 

[Anonymous]

@az38 

Thanks for sticking with me!    Strange behavior on this.      If I select the "true" values the shipping table matches those with the same month in the Orders table.    (shipped month  matches  due month). 

If I select False, it is showing records that actuall DID ship in the same month.    I've checked several examples.    Look at CO-1007881 for example.    

In other words, the True evaluation seems to work...but not the False one.    Is it because of the  "min"  setting?  

[az38]

@Anonymous 

and check all rows with CO-1007881 in shipping and oredrs tables in Data window (if available at the left pane)

[Anonymous]

@az38  I can double check, but the issue seems to be happening on many orders.     Odd...because the "true" value works.     

But the "false" value is erroneously saying "false" when the value should be "true".  

Would adding in some sort if statment help either in the VAR  or  after RETURN.   Something that said "if the ship date is within the range of the due date then "yes", if not then "no".     I realize that is sort of what the current formula is doing...just grasping for straws!).  

Here is another example: 

[az38 · Accepted solution]

@Anonymous 

ok. let's try another technique, for example, add column to Shipping date

Column = 
var _thisDate = EARLIER('Shipping Table'[Shipping Date])
var _cnt = CALCULATE(COUNTROWS('Orders Table'), FILTER(ALL('Orders Table'), 'Orders Table'[Order] = EARLIER('Shipping Table'[Order]) && YEAR('Orders Table'[due date]) = YEAR(_thisDate) && MONTH('Orders Table'[due date]) = MONTH(_thisDate) ) )

RETURN
IF(_cnt > 0 , TRUE(), FALSE())

[Anonymous]

@az38     Hi AZ  -   I tried that formula and for some reason it wont pick up any table or column names after EARLIER.     

When I type something it in, it just appears as grey text... ? 

[az38]

@Anonymous 

yes, my bad. try without EARLIER

Column = 
var _thisDate = 'Shipping Table'[Shipping Date]
var _order = 'Shipping Table'[Order]
var _cnt = CALCULATE(COUNTROWS('Orders Table'), FILTER(ALL('Orders Table'), 'Orders Table'[Order] = _order && YEAR('Orders Table'[due date]) = YEAR(_thisDate) && MONTH('Orders Table'[due date]) = MONTH(_thisDate) ) )

RETURN
IF(_cnt > 0 , TRUE(), FALSE())

[Anonymous]

@az38 

A MILLION KUDOS....THIS SEEMS TO WORKING!   

Here is the final formula that I used.   I add this formula (column formula) to my shipping table and everything seems to be matching up.     I can't think you enough and will certainly mark as solved and kudos.   This is very much a real world situation as I owe my boss this report tomorrow.    Many thanks again! 

Match Due Date =

var _thisDate = 'Flu_Shipped Orders'[Date Shipped]

var _order = 'Flu_Shipped Orders'[Order]

var _cnt = CALCULATE(COUNTROWS(Sales_Orders_for_Charts), FILTER(ALL(Sales_Orders_for_Charts), Sales_Orders_for_Charts[Order] = _order && YEAR(Sales_Orders_for_Charts[Due Date]) = YEAR(_thisDate) && MONTH(Sales_Orders_for_Charts[Due Date]) = MONTH(_thisDate) ) )

RETURN IF(_cnt > 0 , TRUE(), FALSE())

[Anonymous]

@az38 

A MILLION KUDOS....THIS SEEMS TO WORKING!   

Here is the final formula that I used.   I add this formula (column formula) to my shipping table and everything seems to be matching up.     I can't think you enough and will certainly mark as solved and kudos.   This is very much a real world situation as I owe my boss this report tomorrow.    Many thanks again! 

Match Due Date =

var _thisDate = 'Flu_Shipped Orders'[Date Shipped]

var _order = 'Flu_Shipped Orders'[Order]

var _cnt = CALCULATE(COUNTROWS(Sales_Orders_for_Charts), FILTER(ALL(Sales_Orders_for_Charts), Sales_Orders_for_Charts[Order] = _order && YEAR(Sales_Orders_for_Charts[Due Date]) = YEAR(_thisDate) && MONTH(Sales_Orders_for_Charts[Due Date]) = MONTH(_thisDate) ) )

RETURN IF(_cnt > 0 , TRUE(), FALSE())

[az38]

@Anonymous 

drop both columns - due and shipping date into one visual to debug.