Multiple Date Trouble

[BernerDogs12]

Hey All,

I have two data tables linked by Sale number. Let's say the data looks like this:

[TABLE="width: 250"]
<tbody>[TR]
[TD]Sale Number[/TD]
[TD]Sale Date[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]01/01/2016[/TD]
[/TR]
[TR]
[TD]23456[/TD]
[TD]02/02/2016[/TD]
[/TR]
[TR]
[TD]34567[/TD]
[TD]03/03/2016[/TD]
[/TR]
[TR]
[TD]45678[/TD]
[TD]04/04/2016[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 250"]
<tbody>[TR]
[TD]Sale Number[/TD]
[TD]Change Date[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]01/02/2016[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]01/08/2016[/TD]
[/TR]
[TR]
[TD]12345[/TD]
[TD]01/20/2016[/TD]
[/TR]
[TR]
[TD]23456[/TD]
[TD]02/08/2016[/TD]
[/TR]
[TR]
[TD]34567[/TD]
[TD]03/09/2016[/TD]
[/TR]
[TR]
[TD]45678[/TD]
[TD]04/21/2016[/TD]
[/TR]
</tbody>[/TABLE]


As show, each sale number has only one sale date, but each sale number could have multiple change dates, which shows changes to overall revenue after sale.

I'm trying to pull changes to revenue by maturity and have the data tables (both pulling from the same SQL server) linked by a relationship between Sale number on both.

I'm trying to write a formula in a calculated column that will show how many days after the sale a change was made (Change Date-Sale Date) but I'm getting an error. It goes to say this spreadsheet is being designed for people who aren't good with excel, so I can't explore options outside a calculated column.

Please help!

 

[queuesmef]

If Table1 is Sales No & Sale Date, and Table2 is Sales No & Change date: If in Excel - I would perform a lookup on Table2 (from Table1) to see if it can find a SALE Date...if it does, place that date in the 2nd column in the SALES TABLE ( Headings would look like this: Col A= SalesNumber; Col B = SaleDate (lookup); Col C = ChangeDate; Col D = DateDif). If there is no lookup value for - make it '0', otherwise put the lookup value in Table2...I then used the DateDif formula to compute the number of days between the 2 dates [ =datedif(salesDate, ChangeDate, "D" ; where D stands for the number of days between the 2 points (note: smaller date value MUST come before the Larger date value!]

 

[akice]

Which change date do you want to compare to the Sale Date? The last one listed? If these two tables are only ones in the model, you can try as a Calculated Column in the Sales table like this:

=VAR LastChangeDate =
    LASTDATE ( RELATEDTABLE ( ChangeTable[Change Date] ) )
RETURN
    LastChangeDate - Sale[Sale Date]

or in the Change table like this:

= ChangeTable[Change Date] - RELATED ( Sale[Sale Date] )