Need help converting SQL to DAX

[Anonymous]

Hello! I am a newbie to power bi. Just dangerous enough to do a few visuals. But... I am not a coder!

Can anyone help me change this from sql to dax?

1. DECLARE @ASOFDATE DATETIME
2. SET @ASOFDATE = '2016-12-31'
3.  
4. SELECT
5. ITEMNMBR AS [Item Number],
6. TRXLOCTN AS [Location],
7. SUM(Quantity) AS Quantity,
8. SUM([Extended Cost]) AS [Total Cost]
9.  
10. FROM
11.  
12. (SELECT ITEMNMBR,
13.  
14. (dbo.IV10200.QTYRECVD -
15. ISNULL((SELECT SUM(QTYSOLD) FROM IV10201
16.         WHERE SRCRCTSEQNM = IV10200.RCTSEQNM
17.         AND ITEMNMBR = IV10200.ITEMNMBR
18.         AND DOCDATE <= @ASOFDATE), 0))
19. AS [Quantity],
20.         UNITCOST *
21.             (dbo.IV10200.QTYRECVD -
22.             ISNULL((SELECT SUM(QTYSOLD) FROM IV10201
23.             WHERE SRCRCTSEQNM = IV10200.RCTSEQNM
24.             AND ITEMNMBR = IV10200.ITEMNMBR
25.             AND DOCDATE <= @ASOFDATE), 0))
26. AS [Extended Cost],             
27. TRXLOCTN
28. FROM dbo.IV10200 WHERE DATERECD <@ASOFDATE) AS SOURCE
29. GROUP BY ITEMNMBR, TRXLOCTN

Any help would be huge!

Thank you,

Stephanie

[CNENFRNL · Accepted solution]

@Anonymous , Value.NativeQuery comes to your rescue. In fact, you don't even need to tweak your sql. You might want to refer to this article on passing parameters to sql.

[Anonymous]

Thank you very much!!