Show only values that occur after the first positive value

[relliott]

Hi,

I am struggling to find a solution to my problem and have searched the forum for hours trying to peice together a solution. 

I have a hypothetical table with 3 columns: Date, BusinessID, Sales. Using DAX, I want only show values from the Sales column that occur after each business' first initial positive Sale value. The first positive Sale acts as a trigger for us to start tracking further data, so I want to ignore all negative Sales values until a positive one occurs, then show all. In the below screenshot, the orange column has the desired behavior. 

I need to do this in DAX, because the Sales column is a measure that relies on a date from another table. 

[HotChilli · Accepted solution]

This is a column since I don't have your data or measure.  No guarantees but you get the idea.

TxnsFromPos = VAR _col = CALCULATE(MIN(TableSale[Date]), TableSale[Sales] > 0 ,ALLEXCEPT(TableSale, TableSale[businessiD]))
RETURN  
   if (_col <= TableSale[Date], TableSale[Sales], BLANK())

Could I ask that in future you post your data (not a picture) and any relevant measure code (or pbix).  

Hope it helps.

[Greg_Deckler · Accepted solution]

I am thinking something like the following:

Column = 
  VAR __BusinessID = [BusinessID]
  VAR __Date = [Date]
  VAR __Table = FILTER('Table',[BusinessID] = __BusinessID && [Date] <= __Date)
  VAR __FirstPositive = MINX(FILTER(__Table,[Sales] > 0),[Date])
RETURN
  IF(__Date >= __FirstPositive,[Sales],BLANK())

[relliott]

This worked great! Thanks, Greg! I may have been overthinking it...

[HotChilli · Accepted solution]

This is a column since I don't have your data or measure.  No guarantees but you get the idea.

TxnsFromPos = VAR _col = CALCULATE(MIN(TableSale[Date]), TableSale[Sales] > 0 ,ALLEXCEPT(TableSale, TableSale[businessiD]))
RETURN  
   if (_col <= TableSale[Date], TableSale[Sales], BLANK())

Could I ask that in future you post your data (not a picture) and any relevant measure code (or pbix).  

Hope it helps.

[relliott]

Thanks! I will share the pbix in the future. good advice!