Create a calculated table with the date of when the status was changed

[viber15]

Hi all,

I'm trying to create a calculated table filter the latest status for each item and the date that status was last changed for each item but I'm having trouble to achieve it.

Here is an example of the table I have

ID	Status	Department	Date
1	Open	Finance	2020-01-01
1	Closed	Finance	2020-02-01
1	Closed	HR	2020-03-01

I tried using this DAX formula:

Summary Table = SUMMARIZE (

'Table',

'Table'[ID],

"State", LASTNONBLANK('Table'[State], 1),

"Date", LASTDATE('Table'[Date].[Date])

)

But with that I get this result, showing the date as the latest date available for that ID, instead of showing the date for time the state was last changed

ID	State	Date
1	Closed	2020-03-01

But what I need is for it to show the date of the latest State change, like this

ID	State	Date
1	Closed	2020-02-01

I also tried changing it to FIRSTDATE and then it shows the very first date for that ID (2020-01-01).

Thanks in advance!

[ryan_mayu]

@viber15 

Please try this

Table 2 = 
VAR TBL=SUMMARIZE('Table (2)','Table (2)'[ID],'Table (2)'[DEPARTMENT],"COUNT",COUNTROWS('Table (2)'),"DATE",MAX('Table (2)'[DATE]),"STATUS",MAXX(FILTER('Table (2)','Table (2)'[ID]=EARLIER('Table (2)'[ID])&&'Table (2)'[DATE]=MAX('Table (2)'[DATE])),'Table (2)'[STATUS]))
RETURN SUMMARIZE(FILTER(TBL,[COUNT]>1),'Table (2)'[ID],[DATE],[STATUS])

[viber15]

Hi Ryan,

Thanks for the quick answer!

Unfortunately, even though it works for this simple table I provided as a sample, it doesn't work for my own table, which actually is more complex than that one.

My table has 16 columns and the date in it changes when there is a change on any of the columns. When I use the formula provided and I add all the columns it has (other than the status and date) before the "COUNT",COUNTROWS('Table (2)') section, the calculated table returns empty.

[ryan_mayu]

@viber15 

Since I am not able to see your full data, maybe you can try create a column first

COUNT = COUNTX(FILTER('Table','Table'[ID]=EARLIER('Table'[ID])&&'Table'[DEPARTMENT]=EARLIER('Table'[DEPARTMENT])),'Table'[ID])

Then create table again.

Table 2 = SELECTCOLUMNS(FILTER('Table','Table'[STATUS]="CLOSED"&&'Table'[COUNT]>1),"ID",'Table'[ID],"status",'Table'[STATUS],"department",'Table'[DEPARTMENT],"date",'Table'[DATE])