Forum Discussion

beer_not_bear's avatar
beer_not_bear
Regular Visitor
5 years ago

Filter expression by another column

Hello,   The following sales table exists: [Entry DateTime], [Customer], [Item Sales Qty.], [Sales Amount].   Task: For a specified period (e.g. 11/15/20 - 12/25/20, column [Entry DateTime]), t...
Need Help
Show and Tell
Greg_Deckler's avatar
Greg_Deckler
Community Champion
5 years ago

beer_not_bear - Difficult to say with specificity without testing out something. Sample data would help greatly. However, you should be able to find a MIN date using variables or EARLIER for a customer. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous