Need Help in Dax

[Anonymous]

Hi ,

I am trying to write a DAX based on the below SQL query logic. But struggilling in the date patrt. Any help would really appriciated.

SELECT IIF(Month(estimated_close_date)=1 AND Year(estimated_close_date) = Year(getdate())-1,
Sum(price_per_unit*quantity)+
Sum(onetime_charge),IIF(Year(estimated_close_date) = Year(getdate())-1 AND
(estimated_close_date < estimatedendbilldate) AND
Month(estimated_close_date) <1 AND
(Month(estimated_close_date)+IIF(contract_period_unit=2,contract_period,
IIF(contract_period_unit=3,contract_period.Value*12,0)))>1,
(Sum(price_per_unit*quantity)),0)) AS JAn
from xyx

Many thanks Advance

[Anonymous]

@Anonymous 

Try this below dax

JAn :=
VAR YearToDate = YEAR(TODAY()) - 1
VAR LastDayOfYear = DATE(YEAR(TODAY()) - 1, 12, 31)
VAR EstimatedCloseDate = EARLIER(xyx[estimated_close_date])
VAR EstimatedEndBillDate = EARLIER(xyx[estimatedendbilldate])
VAR ContractPeriodUnit = EARLIER(xyx[contract_period_unit])
VAR ContractPeriod = EARLIER(xyx[contract_period])
RETURN
IF(
MONTH(EstimatedCloseDate) = 1 &&
YEAR(EstimatedCloseDate) = YearToDate,
SUM(xyx[price_per_unit] * xyx[quantity]) +
SUM(xyx[onetime_charge]),
IF(
YEAR(EstimatedCloseDate) = YearToDate &&
EstimatedCloseDate < EstimatedEndBillDate &&
MONTH(EstimatedCloseDate) < 1 &&
MONTH(EstimatedCloseDate) + IF(
ContractPeriodUnit = 2,
ContractPeriod,
IF(
ContractPeriodUnit = 3,
ContractPeriod.Value * 12,
0
)
) > 1,
SUM(xyx[price_per_unit] * xyx[quantity]),
0
)
)