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 IDState Previous_State
1001backlog 
1001InprogressBacklog
1001DoneInprogress
2001backlog 
2001InprogressBacklog
2001DoneInprogress

 

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 IDState Previous_State
1001backlog 
1001InprogressDone
1001DoneBacklog
2001backlog 
2001InprogressDone
2001DoneBacklog


Please help,

Thanks,

 

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,

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Check this

       

      • 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.

  • DataNinja777's avatar
    DataNinja777
    Super User
    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,