Forum Discussion
Calculate time difference between dates in different tables
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.
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
6 Replies
- LivioLanzo
Solution Sage
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] )
)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.
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!
v-lili6-msftCommunity Support
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.
Best Regards,
Lin
- 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
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
