topic Dax in DAX Commands and Tips

Dax

RickF83 — Mon, 16 Jan 2023 12:31:54 GMT

I have a table which has purchase and sales for each item. I need a Dax formula which looks at an item and the earliest date which has "Purchase" (so this is the starting point) and then any add sales to deduct from total and if purchase add that on. This will allow me to identify out of stock days.

Can anyone help?

Re: Dax

Greg_Deckler — Mon, 16 Jan 2023 13:05:39 GMT

RickF83 What is your expected output? If I am interpreting this correctly, you could do something like this:

Measure = VAR __Item = MAX('Table'[Item No_] VAR __CurrentDate = MAX('Table'[Document Date]) VAR __FirstDate = MINX(FILTER(ALL('Table'),[Item No_] = __Item && [PurchoseORSale] = "Purchase"),[Document Date]) VAR __Purchases = SUMX(FILTER(ALL('Table'),[Document Date] >= __FirstDate && [Document Date] <= __CurrentDate && [PurchaseORSale] = "Purchase"),[Invoiced Quantity]) VAR __Sales = SUMX(FILTER(ALL('Table'),[Document Date] >= __FirstDate && [Document Date] <= __CurrentDate && [PurchaseORSale] = "Sale"),[Invoiced Quantity]) VAR __Result = __Purchase - __Sales RETURN __Result

Re: Dax

RickF83 — Mon, 16 Jan 2023 14:17:17 GMT

Greg_Deckler its coming up with an error.

The syntax for 'VAR' is incorrect. (DAX( VAR __Item = MAX('Item Ledger Entry'[Item No_] VAR __CurrentDate = MAX('Item Ledger Entry'[Document Date]) VAR __FirstDate = MINX(FILTER(ALL('Item Ledger Entry'),[Item No_] = __Item && [PurchoseORSale] = "Purchase"),[Document Date]) VAR __Purchases = SUMX(FILTER(ALL('Item Ledger Entry'),[Document Date] >= __FirstDate && [Document Date] <= __CurrentDate && [PurchaseORSale] = "Purchase"),[Invoiced Quantity]) VAR __Sales = SUMX(FILTER(ALL('Item Ledger Entry'),[Document Date] >= __FirstDate && [Document Date] <= __CurrentDate && [PurchaseORSale] = "Sale"),[Invoiced Quantity]) VAR __Result = __Purchase - __SalesRETURN __Result)).

the expected out put is to see where items have gone out of stock, so I can fill in the OOS Days and calculate predicted lost sales.

Re: Dax

Greg_Deckler — Mon, 16 Jan 2023 15:39:18 GMT

RickF83 Missed a paren:

Measure = VAR __Item = MAX('Table'[Item No_]) VAR __CurrentDate = MAX('Table'[Document Date]) VAR __FirstDate = MINX(FILTER(ALL('Table'),[Item No_] = __Item && [PurchoseORSale] = "Purchase"),[Document Date]) VAR __Purchases = SUMX(FILTER(ALL('Table'),[Document Date] >= __FirstDate && [Document Date] <= __CurrentDate && [PurchaseORSale] = "Purchase"),[Invoiced Quantity]) VAR __Sales = SUMX(FILTER(ALL('Table'),[Document Date] >= __FirstDate && [Document Date] <= __CurrentDate && [PurchaseORSale] = "Sale"),[Invoiced Quantity]) VAR __Result = __Purchase - __Sales RETURN __Result