Group by and having based on parameter

[Anonymous]

I have the following sql query which brings the accurate results as expected but not in Power BI.

select v.ITEMID
,v.ITEMGROUPID
,v.UNITID
,v.INVENTSITEID
,v.INVENTLOCATIONID
,v.WMSLOCATIONID
,v.INVENTSERIALID
,SUM(v.QTYAGING)
,MAX(v.RECEIPTDATE)
from AXPInventoryCube_InventAgingReceiptView v

where
v.Company = 'Microshot'
and v.ITEMID = 'Item01'
and v.INVENTLOCATIONID = 'Warehouse'
and v.RECEIPTDATE < GETDATE() and v.RECEIPTDATE > GETDATE() - 45 --Period 1
--and v.RECEIPTDATE <= GETDATE() - 45 and v.RECEIPTDATE > GETDATE() - 75 --Period 2
--and v.RECEIPTDATE <= GETDATE() - 75 and v.RECEIPTDATE > GETDATE() - 105 --Period 3
--and v.RECEIPTDATE <= GETDATE() - 105 and v.RECEIPTDATE > GETDATE() - 135 --Period 4
group by v.ITEMID
,v.ITEMGROUPID
,v.UNITID
,v.INVENTSITEID
,v.INVENTLOCATIONID
,v.WMSLOCATIONID
,v.INVENTSERIALID
,v.VOUCHER
having SUM(v.QTYAGING) > 0

I wrote it as DAX in InventoryOnHand table column like:

(InventoryReceiptValue is the same table as used in above sql)

QtyAgingPeriod1 =

var receiptQty = SUMX(FILTER(SUMMARIZE(InventoryReceiptValue,

InventoryReceiptValue[DATAAREAID],

InventoryReceiptValue[ITEMID],

InventoryReceiptValue[UNITID],

InventoryReceiptValue[ITEMGROUPID],

InventoryReceiptValue[INVENTSITEID],

InventoryReceiptValue[INVENTLOCATIONID],

InventoryReceiptValue[WMSLOCATIONID],

InventoryReceiptValue[INVENTSERIALID],

InventoryReceiptValue[VOUCHER],

"QtyAging", SUM(InventoryReceiptValue[QTYAGING]),

"ReceiptDate", MAX(InventoryReceiptValue[RECEIPTDATE].[Date])),

[DATAAREAID] = InventoryOnHand[DATAAREAID]

&& [ITEMID] = InventoryOnHand[ITEMID]

&& [ITEMGROUPID] = InventoryOnHand[ITEMGROUPID]

&& [UNITID] = InventoryOnHand[UNITID]

&& [INVENTSITEID] = InventoryOnHand[SITE]

&& [INVENTLOCATIONID] = InventoryOnHand[WAREHOUSE]

&& [WMSLOCATIONID] = InventoryOnHand[LOCATION]

&& [INVENTSERIALID] = InventoryOnHand[SERIAL NUMBER]

&& [QTYAGING] > 0

&& [ReceiptDate] < TODAY()

&&[ReceiptDate] > TODAY() - 45)

,[QTYAGING])

var qty = receiptQty

return qty

It looks like it is not filtering the ReceiptDate, please point out if there is any other issue.

[amitchandak · Accepted solution]

@Anonymous .  You have create measure for each requirement

Try with these modifications

QTYAGING m=

SUMX(FILTER(SUMMARIZE(Filter(InventoryReceiptValue,[ReceiptDate] < TODAY()
&&[ReceiptDate] > TODAY() - 45) ,
InventoryReceiptValue[DATAAREAID],
InventoryReceiptValue[ITEMID],
InventoryReceiptValue[UNITID],
InventoryReceiptValue[ITEMGROUPID],
InventoryReceiptValue[INVENTSITEID],
InventoryReceiptValue[INVENTLOCATIONID],
InventoryReceiptValue[WMSLOCATIONID],
InventoryReceiptValue[INVENTSERIALID],
InventoryReceiptValue[VOUCHER],
"QtyAging", SUM(InventoryReceiptValue[QTYAGING]),
"ReceiptDate", MAX(InventoryReceiptValue[RECEIPTDATE].[Date])),
[QTYAGING] > 0 )
,[QTYAGING])

ReceiptDate m =

=SUMX(FILTER(SUMMARIZE(Filter(InventoryReceiptValue,[ReceiptDate] < TODAY()
&&[ReceiptDate] > TODAY() - 45) ,
InventoryReceiptValue[DATAAREAID],
InventoryReceiptValue[ITEMID],
InventoryReceiptValue[UNITID],
InventoryReceiptValue[ITEMGROUPID],
InventoryReceiptValue[INVENTSITEID],
InventoryReceiptValue[INVENTLOCATIONID],
InventoryReceiptValue[WMSLOCATIONID],
InventoryReceiptValue[INVENTSERIALID],
InventoryReceiptValue[VOUCHER],
"QtyAging", SUM(InventoryReceiptValue[QTYAGING]),
"ReceiptDate", MAX(InventoryReceiptValue[RECEIPTDATE].[Date])),
[QTYAGING] > 0 )
,[ReceiptDate])

[Anonymous]

You are a super star. Thank you so very much.
I had to pass the date filtered table in summarize method.