IF condition for a datetime between two datetimes from another table

[marcospaulo]

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?

[parry2k]

@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

[marcospaulo]

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

[v-xjiin-msft]

@marcospaulo

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]&"-"&quotation[Company]&"-"&quotation[Period]

Thanks,
Xi Jin.

[ricardocamargos]

Hi @marcospaulo,

I think you can append those tables and work as one single table.

If I can help you let me know.

Thanks.