Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

New column to populate previous value

I have a table similar to below, i would like to add a 'new column' to popular 'Previous Status' by ID and Date. In Excel, I can sort by ID and Date, then insert a funcation = previous value.    how to do it in DAX? thanks. 

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Anonymous,

    Right Click your table in Fields panel and select “New Column”, then enter the following DAX.

    Previous Status =
var previous= CALCULATE(FIRSTNONBLANK(Table[Status],""),FILTER(Table,Table[ID]=EARLIER(Table[ID])&& Table[Date]<EARLIER(Table[Date])))
return
 IF(ISBLANK(previous),BLANK(), previous)



    Regards,
    Lydia

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Anonymous,

    Right Click your table in Fields panel and select “New Column”, then enter the following DAX.

    Previous Status =
var previous= CALCULATE(FIRSTNONBLANK(Table[Status],""),FILTER(Table,Table[ID]=EARLIER(Table[ID])&& Table[Date]<EARLIER(Table[Date])))
return
 IF(ISBLANK(previous),BLANK(), previous)



    Regards,
    Lydia

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      on the  'solved solution' I get: 

      The syntax for 'Table' is incorrect. (DAX(var previous= CALCULATE(FIRSTNONBLANK(Table[Status],""),FILTER(Table,Table[ID]=EARLIER(Table[ID])&& Table[Date]<EARLIER(Table[Date])))return IF(ISBLANK(previous),BLANK(), previous))).

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    I have the same requirement, but the IDs & Dates are not sorted. Can you provide a formula which works even on unsorted data