Dax

[RickF83]

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?

[Greg_Deckler]

@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

[RickF83]

@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.

[Greg_Deckler]

@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