Partial received quantity to be deducting from the Order quantity till full quantity received

[pankajj]

Hi Team of Experts!

I need help in displaying Stock Alert.

My data sample and expected result is as follows:

ORDER-LINE	ORDER QTY	Demand_Order	ALLOTTED_QTY	QTY_DIFFERENCE	PROMISE DATE	Supply_Date	Stock Alert	Message
SO-AB1234/1	100	SO-AB1234/1	20	80	April01 2020	March10 2020	Early	Partial Quantity (20) Arriving Early on March10 2020 for SO-AB1234/1
SO-AB1234/1		SO-AB1234/1	30	50	April01 2020	March20 2020	Early	Partial Quantity (30) Arriving Early on March20 2020 for SO-AB1234/1
SO-AB1234/1		SO-AB1234/1	40	10	April01 2020	April20 2020	Late	Partial Quantity (40) Arriving Late on April20 2020 for SO-AB1234/1
SO-AB1234/1		SO-AB1234/1	10	0	April01 2020	May15 2020	Late	Final Quantity (10) Arriving Late on May15 2020 for SO-AB1234/1

My following Query is giving me error.

if[TOTAL_SHIPPED_QTY]>=[ORDER_QTY]
then "FULL QUANTITY SHIPPED" else
if[Supply_Date]<> null then if([QTY_DIFFERENCE]>0
and
[REMAIN_QTY]=[ALLOTTED_QTY]) then
"Full Quantity" & "(" & Number.ToText[ALLOTTED_QTY] & ")" & " Arriving on" & " " & [Supply_Date] & " " & "for" & [#"ORDER-LINE"] else

if([REMAIN_QTY]>[ALLOTTED_QTY]) and [QTY_DIFFERENCE]>0 then
"Partial Quantity" & "(" & Number.ToText[ALLOTTED_QTY] & ")" & " Arriving on" & " " & [Supply_Date] & " " & "for" & [#"ORDER-LINE"] else

if([REMAIN_QTY]>[ALLOTTED_QTY]) and [QTY_DIFFERENCE] = 0 then
"Final Quantity" & "(" & Number.ToText[ALLOTTED_QTY] & ")" & " Arriving on" & " " & [Supply_Date] & " " & "for" & [#"ORDER-LINE"]

else "NO SUPPLY DATE"
else
[Stock Alert]

Will really appreciate kind support of the great community.

Thanks & Best regards,

PG

[amitchandak]

If in power Bi comes in ()  and and is && and Or is ||

Corrected the formula, But you need to rework on the logic still

if([TOTAL_SHIPPED_QTY]>=[ORDER_QTY]
, "FULL QUANTITY SHIPPED" ,
	if([Supply_Date]<> null,
		if([QTY_DIFFERENCE]>0
		&& [REMAIN_QTY]=[ALLOTTED_QTY]) &&  "Full Quantity" & "(" & Number.ToText[ALLOTTED_QTY] & ")" & " Arriving on" & " " & [Supply_Date] & " " & "for" & [#"ORDER-LINE"] ,
			if([REMAIN_QTY]>[ALLOTTED_QTY] && [QTY_DIFFERENCE]>0 ,
				"Partial Quantity" & "(" & Number.ToText[ALLOTTED_QTY] & ")" & " Arriving on" & " " & [Supply_Date] & " " & "for" & [#"ORDER-LINE"] ,
					if([REMAIN_QTY]>[ALLOTTED_QTY] && [QTY_DIFFERENCE] = 0 &&
						"Final Quantity" & "(" & Number.ToText[ALLOTTED_QTY] & ")" & " Arriving on" & " " & [Supply_Date] & " " & "for" & [#"ORDER-LINE"]

, "NO SUPPLY DATE")
else
[Stock Alert])))