Hi, I got this error message when trying to get the L3M of gross margin, see my calculation below:
IF [Date] <= TODAY()
AND
(
DATEDIFF('month',[Date],TODAY())=1
or
DATEDIFF('month',[Date],TODAY())=2
or
DATEDIFF('month',[Date],TODAY())=3
)
then [Gross Margin] END
And the calculation for [Gross Margin]= SUM([Sales])-SUM([COGS])
Can anyone help me fix the IF statement?
답변 10개
Hello,
You can try below logic:
Sales =
ZN(IF [Order Date] <= TODAY()
AND (DATEDIFF('month',[Order Date],TODAY())<=3
AND DATEDIFF('month',[Order Date],TODAY())>0)
then [Sales] END)
COGS =
ZN(IF [Order Date] <= TODAY()
AND (DATEDIFF('month',[Order Date],TODAY())<=3
AND DATEDIFF('month',[Order Date],TODAY())>0)
then [Profit] END)
Cal = SUM(Sales)- SUM(COGS)
