Was this risk in this status last month?

[Anonymous]

I have a table let's say "Risk status table" with the Risk ID, Status, Start of month, Risk ID and Status and what I want to work out New or same status

Risk ID	Status	Start of month	Risk ID and Status	New or same status
R11	S1	01/01/2020	R11 S1	New Status
R12	S1	01/01/2020	R12 S1	New Status
R11	S1	01/02/2020	R11 S1	Same Status
R12	S2	01/02/2020	R12 S2	New Status
R13	S1	01/02/2020	R13 S1	New Status
R11	S2	01/03/2020	R11 S2	New Status
R12	S3	01/03/2020	R12 S3	New Status
R13	S1	01/03/2020	R13 S1	Same Status
R14	S1	01/03/2020	R14 S1	New Status

Above is an example of data, and what I'd expect the calculated column to be. So the calculated column in Jan is all New status since there is no data before that. In Feb, the Risk ID R11 has a status of S1 which it was also in the previous month, so the calculated column is Same Status, and in Feb R12 has changed to status S2 which it wasn't the previous month so the calculated column is New Status.

The logic I want to use in the new calculated colum (New or same status) is to see if the string in the "Risk ID and Status" column appears in the previous month, if the string does appear in the previous month then it's the Same Stauts, if it does not then its a New Status

[Greg_Deckler]

@Anonymous - I mocked this up in the standard way, in attached PBIX Table (25) but for some reason it is not recognizing the return as blank (first occurrence). Maybe something is up with my system? Anyway, see if you get similar results for first occurrences being "Same Status".

Column = 
    VAR __PreviousDate = MAXX(FILTER('Table (25)',[Risk ID]=EARLIER([Risk ID]) && [Start of month]<EARLIER([Start of month])),[Start of month])
    VAR __Previous = MAXX(FILTER('Table (25)',[Risk ID]=EARLIER([Risk ID]) && [Start of month]=__PreviousDate),[Status])
RETURN
    IF(ISBLANK(__PreviousDate) || [Status]=__Previous,"Same Status","New Status")

[Greg_Deckler · Accepted solution]

@Anonymous - This worked, updated PBIX attached.

Column = 
    VAR __PreviousDate = MAXX(FILTER('Table (25)',[Risk ID]=EARLIER([Risk ID]) && [Start of month]<EARLIER([Start of month])),[Start of month])
    VAR __Previous = MAXX(FILTER('Table (25)',[Risk ID]=EARLIER([Risk ID]) && [Start of month]=__PreviousDate),[Status])
    VAR __Count = COUNTX(FILTER('Table (25)',[Risk ID]=EARLIER([Risk ID]) && [Start of month]<EARLIER([Start of month])),[Risk ID])
RETURN
    IF(__Count<0 || [Status]=__Previous,"Same Status","New Status")

[amitchandak]

@Anonymous , create a new column like

new column =
var _date = maxx(filter(table,[Risk ID] = earlier([Risk ID]) && [Start of month] <earlier([Start of month])),[Start of month])
var _st =maxx(filter(table,[Risk ID] = earlier([Risk ID]) && [Start of month] =_date),[Status])
return
if(isblank(_st), "New Status", if(_st = [Status] ,"Same Status", "New Status"))