Forum Discussion

Datafruit's avatar
Datafruit
Helper I
3 years ago
Solved

Measure to return multiple values

Hi   I have a challenge and cannot seem to get over this one. I have a need to create a table, an example can be seen below.  So the idea is to display per id the transactions and current amounts ...
  • Greg_Deckler's avatar
    3 years ago

    Datafruit This is essentiall MTBF linked below, essentially:

    previous transaction datetime column = 
      VAR __curTrans = [transaction datetime]
    RETURN
      MAXX(FILTER('Table',[transaction datetime] < __curTrans),[transaction datetime])
    
    and
    
    previous transaction datetime amount column = 
      VAR __curTrans = [transaction datetime]
      VAR __prevTrans = MAXX(FILTER('Table',[transaction datetime] < __curTrans),[transaction datetime])
    RETURN
      MAXX(FILTER('Table',[transaction datetime] = __prevTrans),[current transaction amount])
    

     

    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