Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

How to create a column for Previous Value

Hi All,    Expected Output: Workitem ID State  Previous_State 1001 backlog   1001 Inprogress Backlog 1001 Done Inprogress 2001 backlog   2001 Inprogress Backlog 20...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Sure, Under transform data >click add column >index column from 1

     

     

  • DataNinja777's avatar
    DataNinja777
    2 years ago

    Hi Anonymous 

     

    There are multiple ways to produce you required output, and one of them is as follows:

    Create index dimension table which also has previous row side by side.  

    Create a relationship between your index dimension table and your fact table.  

    Create a calculated column which produces your required output like below:

    I attach an example pbix file.  

    Best regards,

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi, Thanks for the reply but I dont think we can use previous on a dimension like previous(state) .

 

Please can some one help me with the exact result which i am expecting as this is an important work which has dependencies.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
2 years ago

Anonymous Do you have anything that defines "before"? Can you add an Index? If so you can get previous value using a variation of MTBF. 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 ) * 1.