Calculate time difference between dates in different tables

[Daveed1973]

Not sure if this may be easier in SQL or Power BI but this is what I need to be able to achieve.

I have two different tables in BI desktop, one has a list of removal jobs for products and the other has a list of dates for the products when they were cleaned. So for example a product is removed from a site and is then cleaned. There will therefore be a removal date and also a date when it was cleaned.

For each of these removals for the products (these have unique serial numbers) I need the next clean date for it and calculate how long it took for the products to be cleaned after they were removed.

Table 1 for the removals will have a sturcture like this...

Serial Number, Product Type, Removal Date

111111               Product 1          14/08/2018

111111               Product 1          17/08/2018

222222               Product 2          11/08/2018

333333               Product 3          16/08/2018

Table 2 for the cleaning info will look like this...

Serial Number, Product Type,  Clean Date

111111               Product 1          15/08/2018

111111               Product 1          20/08/2018

222222               Product 2          14/08/2018

333333               Product 3          18/08/2018

The results of this would show me something like this so that I can then work out averages for each product type

Serial Number, Product Type, Days to Clean

111111               Product 1          1

111111               Product 1          3

222222               Product 2          3

333333               Product 3          2

How to I take the serial number and and date from table 1 and find the next clean date for the same serial number in table 2 to work th above out?

Any help would be appreciated.

[v-lili6-msft]

hi, @Daveed1973

     Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Best Regards,

Lin

[Daveed1973 · Accepted solution]

I ended up having to do this in SQL instead as I was finding Power BI was struggling with the amount of data. I did it using an OUTER APPLY similar to this.

FROM tblRemovals r
OUTER APPLY (SELECT TOP 1 DateCleaned FROM tblCleans c WHERE c.SerialNumber = r.SerialNumber AND DateCleaned >= DateRemoved ORDER BY DateCleaned) AS cln

[v-lili6-msft]

hi,@Daveed1973

      If there are multiple rows of the same product in the table 1, how to match to clean date in table 2?

So there are two solutions:

1.

If It matches to the date that the latest date after removal date, you can use this for to find the clean date:

Min clean date = CALCULATE(MIN(Table2[Clean Date]),FILTER(Table2,Table1[Serial Number]=Table2[Serial Number]&&Table1[ Product Type]=Table2[ Product Type]&&Table2[Clean Date]>Table1[ Removal Date]))

then use datediff function to calculate days

2.

If It doesn't match to the date that the latest date after removal date, you can add an index column for two table

and then use lookupvalue function to find the clean date:

Clean date = LOOKUPVALUE(Table2[Clean Date],Table2[Index],Table1[Index])

then use datediff function to calculate days like this 

Days to Clean = DATEDIFF(Table1[ Removal Date],Table1[Clean date],DAY)

Result:

here is pbix, please try it.

https://www.dropbox.com/s/z0mdpqqm8hlsiwy/Calculate%20time%20difference%20between%20dates%20in%20dif...

Best Regards,

Lin

[LivioLanzo]

You have different ways to go about it

one solution can be adding a calculating column in the removal table to get the next clean date and then do the difference

Next Clean Date =
CALCULATE (
    MIN ( Cleans[Clean Date] ),
    Cleans[Product Type] = EARLIER ( Removals[Product Type] ),
    Cleans[Serial Number] = EARLIER ( Removals[Serial Number] ),
    Cleans[Clean Date] > EARLIER ( Removals[Removal Date] )
)

[Daveed1973]

Unfortunately I seem to be having issues with this and I'm guessing its due to the number of rows in the tables, around 150k in each.

As soon as I add the calculated column as per your suggestion the pbix just grinds to halt trying to refresh and then I get en error saying that there is not enough memory to complete the operation.

[Anonymous]

Hi @Daveed1973 ,

Did you end up not using PowerBI anymore? We have large data pools and it looks like we will outgrow PowerBI with its current limitations in about 4-5 years. Let me know what solution you're currently using if you could! Greatly appreciated!