Forum Discussion

adam_mac's avatar
adam_mac
Icon for Helper I rankHelper I
5 years ago
Solved

Power Query: Checking if a trial customer has fully purchased trial product

Hi, i have a dataset that captures if a customer has purchased a trial of a product, if they purchased at a discount, or if they fully purchased.   What i would like to do is to create an additiona...
Need Help
  • adam_mac's avatar
    adam_mac
    5 years ago

    Solved the issue from a reddit user by creating a calculated table:

     

    tbl2 = var tbl = addcolumns( SUMMARIZE( filter('Table','Table'[PurchaseMethod]="Full Purchase"), 'Table'[Customer ID]), "blank",'Table'[Customer ID]) return addcolumns( SUMMARIZE( filter('Table', 'Table'[Product Name]='Table'[Product Name] &&'Table'[PurchaseMethod]="Full Purchase" &&'Table'[Customer ID] in summarize(filter(tbl,[Customer ID]),'Table'[Customer ID]) ), 'Table'[Product Name],'Table'[Customer ID],'Table'[PurchaseMethod]), "test",1)

     

    and adding a column to orginal table:

    FullPurchaseMade = if('Table'[PurchaseMethod]="Trial",lookupvalue(tbl2[test],tbl2[Product Name],'Table'[Product Name],tbl2[Customer ID],'Table'[Customer ID]),blank())

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
5 years ago

adam_mac You should be able to use the technique here: 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