Hi, can anyone help me to transform below SQL formula to DAX?:
-------------------------------------------------------------------------------------------------------------------------------------------------------------
QTY and Value Financial
while select sum(Qty), sum(CostAmountPosted), sum(CostAmountAdjustment),sum(costAmountPhysical),
itemId from inventTrans index itemIdx
group by itemId
where (inventTrans.DateFinancial <= transDate &&
inventTrans.DateFinancial != datenull())
{
if(inventTrans.Qty != 0 || (inventTrans.CostAmountAdjustment + inventTrans.CostAmountPosted) != 0)
{
select sum(CostAmountAdjustment) from settlement
group by itemId where settlement.ItemId == inventTrans.ItemId &&
settlement.TransDate <= transDate &&
settlement.SettleModel != inventSettlemodel::PhysicalValue;
inventTrans.Qty
settlement.CostAmountAdjustment + inventTrans.CostAmountPosted
}
}
-------------------------------------------------------------------------------------------------------------------------------------------------------------
QTY and Value Physical
while select sum(Qty), sum(CostAmountPosted), sum(CostAmountAdjustment),sum(costAmountPhysical),
itemId from inventTrans
group by itemId
where
(inventTrans.DateFinancial == dateNull() ||
(inventTrans.DateFinancial > transDate)
&&
(inventTrans.DatePhysical != dateNull() &&
inventTrans.DatePhysical <= transDate))
{
inventTrans.Qty
inventTrans.costAmountPhysical
}
-------------------------------------------------------------------------------------------------------------------------------------------------------------
PZ is later than FV
while select sum(Qty), sum(CostAmountPhysical),
itemId from inventTrans
group by itemId
where
inventTrans.DateFinancial <= transDate &&
inventTrans.DateFinancial != datenull() &&
inventTrans.DatePhysical > transDate
{
inventTrans.Qty
inventTrans.costAmountPhysical
}
-------------------------------------------------------------------------------------------------------------------------------------------------------------
