Forum Discussion
IF condition for a datetime between two datetimes from another table
Hi,
I have two tables, one with a list of quotations and another with the price update of those products.
I want to compare the price from the quotations with the price updates, the problem is that for every update, all products have their prices updated (many-to-many).
I tried to stablish a period with an initial datetime (the datetime of the update) and a last datetime (the datetime of the next update minus one minute), and with the IF condition, check if the quotation occurred between those datetimes.
The problem is, I cant ref those columns into the IF condition on the first table, the Power BI dont even show those columns to choose, I believe it is because I dont have any relationship between those tables (the relationship would be this same conditional).
I'm using the datetime format because these proccess occur in small periods of time (a few minutes), been necessary to know both date and time of each information.
Could you folks give me a help?
4 Replies
Hi marcospaulo,
I think you can append those tables and work as one single table.
If I can help you let me know.
Thanks.
marcospaulo yes you are not seeing the columns becuase there is no relationship,you need a product table which can be used to set relationship between those two tables and can work from there.
if you share sample data it will help to provide the solution
The first table (quotation) is like this:
Send Date Time Operation Company Product Location Period Volume Price Status 01/02/2018 15:00 Buy P CONV SE/CO jan/18 2,96 -R$ 12,90 Lost 01/02/2018 15:30 Buy D CONV SE/CO jan/18 8,00 -R$ 9,00 Won 01/02/2018 15:30 Buy H CONV SE/CO jan/18 2,05 -R$ 11,90 Lost And the second (pricing) is like this:
Period Date Time Operation Location Product Price jan/18 01/02/2018 14:10:00 Buy SE/CO CONV -R$ 11,90 jan/18 01/02/2018 14:10:00 Sell SE/CO CONV -R$ 9,60 jan/18 01/02/2018 14:10:00 Buy N CONV -R$ 8,90 jan/18 01/02/2018 14:10:00 Buy NE CONV -R$ 11,90 jan/18 01/02/2018 14:10:00 Buy S CONV -R$ 11,90 jan/18 01/02/2018 14:10:00 Sell N CONV -R$ 6,60 jan/18 01/02/2018 14:10:00 Sell NE CONV -R$ 9,60 jan/18 01/02/2018 14:10:00 Sell S CONV -R$ 9,60 jan/18 01/02/2018 15:15:00 Sell SE/CO CONV -R$ 9,60 jan/18 01/02/2018 15:15:00 Buy N CONV -R$ 8,90 jan/18 01/02/2018 15:15:00 Buy NE CONV -R$ 11,90 jan/18 01/02/2018 15:15:00 Buy S CONV -R$ 11,90 jan/18 01/02/2018 15:15:00 Buy SE/CO CONV -R$ 11,90 jan/18 01/02/2018 15:15:00 Sell N CONV -R$ 6,60 jan/18 01/02/2018 15:15:00 Sell NE CONV -R$ 9,60 jan/18 01/02/2018 15:15:00 Sell S CONV -R$ 9,60 In your scenario, what's the logic to compare the price from the quotations with the price updates. For example: let's say the price of the first row in table quotation is -R$ 12,90. Then which one or ones should be compared in table pricing? To compare data between tables, we should get the relation between them first. Even though they are many to many.
Then with many to many relationship in Power BI. Generally, we can combine those columns into one Key column. Then use this key column to make one to one or one to many relationship. Something like:
Key = quotation[Product]&"-""ation[Company]&"-""ation[Period]
Thanks,
Xi Jin.
