Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
}
-------------------------------------------------------------------------------------------------------------------------------------------------------------
up
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
13 | |
11 | |
9 | |
6 |