DAX Create column LAST status based on MAX date by Id

DAX Create column LAST status based on MAX date

I have the following dataset with each id having different Values on different dates. I want to add one new column which should have the latest Value per ID on each row. The solution should work when we apply the explicit filter on the Date entered.

id	value	Dateentered	Latest Date	Latest Value
1	y	10/12/2018
1	x	10/13/2018
2	x	10/14/2018
2	z	10/15/2018
2	y	10/16/2018

I tried using an EARLIER function. The solution was perfectly working without an external filter on Datenetered but it didn't work when we apply a filter on the date entered.

Latest Date = CALCULATE(MAX(Sheet1[Date Entered]),FILTER(Sheet1 , Sheet1[id] = EARLIER(Sheet1[id])))

LatestValue = CALCULATE(MAX(Sheet1[value]),FILTER(Sheet1 , Sheet1[id] = EARLIER(Sheet1[id])),FILTER(Sheet1,Sheet1[Date Entered]=Sheet1[Latest Date]))

Following is the result which we get without external filter.

id	value	dateentered	Latest Date	Latest Value
1	y	10/12/2018	10/13/2018	X
1	x	10/13/2018	10/13/2018	X
2	x	10/14/2018	10/16/2018	Y
2	z	10/15/2018	10/16/2018	Y
2	y	10/16/2018	10/16/2018	Y

But if we filter the records with the date entered < 10/16/2019. The above solution does not work.

We should get the following result.

id	value	date entered	Latest Date	Latest Value
1	y	10/12/2018	10/13/2018	X
1	x	10/13/2018	10/13/2018	X
2	x	10/14/2018	10/15/2018	z
2	z	10/15/2018	10/15/2018	z

Please help me out..

Need Help

dax
6 years ago
Hi shuklarajiva,

You could use measure to achieve this goal, you could refer to my sample for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1216.pbix55 KB

5 Replies

kentyler
Solution Sage
6 years ago
Ah, you've found one of the great Power BI "Traps"..... PREVIOUS() does not mean the previous row... it is misnamed and should be called EARLIER().. it returns the value in the outer filter context.
Power BI does not have a native concept of the "next row" or the "previous row" so you have to hack it.
First step is to add an incremental index in the query editor

then you can write 2 measures that grab the index and the client id from the row that is in the filter context when they are called and use those values to look up the row you are interested in.
Here are the measures
Latest Date =
var index = selectedvalue('Table'[Index])
var current_date = selectedvalue('Table'[Dateentered])
var next_index = index + 1
var client = selectedvalue('Table'[id])
var next_date = calculate(values('Table'[Dateentered]),ALL('table'),'Table'[Index] = next_index,'Table'[id] = client)
var return_date = IF(isblank(next_date),current_date,next_date)
return return_date

Latest Value =
var index = selectedvalue('Table'[Index])
var current_value = SELECTEDVALUE('Table'[value])
var next_index = index +1
var client = selectedvalue('Table'[id])
var next_value = calculate(values('Table'[value]),ALL('table'),'Table'[Index] = next_index,'Table'[id] = client)
var return_value = IF(ISBLANK(next_value),current_value,next_value)
return return_value

you can see they both do the same sort of things
they use "SELECTEDVALUE()" to get the index and the value from the row that is in the filter context when the measure is called
they use CALCULATE() to first apply ALL() to the table, so any other filters applied to it in the interface will be removed and then use the index to get the "next" row
They check and if there is no "next" value, they return the value from the row they're on

I'm a personal Power BI trainer I learn something every time I answer a question.
- Anonymous
  Not applicable
  6 years ago
  
  Hey Kentyler,
  
  I like your approach but this is giving the wrong output. Row no - 4 or Index 2 is incorrect, It should have the Latest date as 10/16/2018 or the Latest value should be Y.
  
  id value dateentered Index Latest Value Latest Date
  1 y 10/12/2018 0 x 10/13/2018
  1 x 10/13/2018 1 x 10/13/2018
  2 x 10/14/2018 2 z 10/15/2018
  2 z 10/15/2018 3 y 10/16/2018
  2 y 10/16/2018 4 y 10/16/2018
  - Anonymous
    Not applicable
    6 years ago
    It is also not working when I apply an external filter. Let's say if I select date entered to only 10/12/2018. I just one row then it should display the latest value as "y".
    
    I might be doing something wrong.

Forum Discussion

DAX Create column LAST status based on MAX date by Id

5 Replies

Related content

Create columns group

Status column in workspace

Creating a date column

Creating a custom column

unpivoting columns and creating datetime column

id	value	dateentered	Index	Latest Value	Latest Date
1	y	10/12/2018	0	x	10/13/2018
1	x	10/13/2018	1	x	10/13/2018
2	x	10/14/2018	2	z	10/15/2018
2	z	10/15/2018	3	y	10/16/2018
2	y	10/16/2018	4	y	10/16/2018