How to create a column for Previous Value

[Naveen112]

Hi All, 

Expected Output:

Workitem ID	State	Previous_State
1001	backlog
1001	Inprogress	Backlog
1001	Done	Inprogress
2001	backlog
2001	Inprogress	Backlog
2001	Done	Inprogress

I am trying to create Previous_State column  based on State .
Written the below DAX but its giving me Done, Backlog instead of  backlog and  Inprogress. 

Previous_State=

CALCULATE (

     MAX('table'[State]) ,

        ALLEXCEPT ( 'table', 'table'[Work Item Id] ),

        'table'[State] < EARLIER('table'[State])

)

Workitem ID	State	Previous_State
1001	backlog
1001	Inprogress	Done
1001	Done	Backlog
2001	backlog
2001	Inprogress	Done
2001	Done	Backlog

Please help,

Thanks,

[Surya9 · Accepted solution]

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

[DataNinja777 · Accepted solution]

Hi @Naveen112 

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,

[Surya9]

Use visual level calculation in home tab > new calculation 
and use previous in measure 

[Surya9]

Check this

[Naveen112]

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.

[Surya9]

Create on index column in a table and create measure as shown in below  image

[Naveen112]

HI @Surya9 , Can you please share me the logic for INDEX please. I can replicate and check thank you

[Surya9 · Accepted solution]

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

[Greg_Deckler]

@Naveen112 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/3395....
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.