How to show prior entry when month missing

[Anonymous]

I am trying to create a columnd to show me prior month status but because my months have gaps in them I am getting blanks

I have the following data. The first 3 are in my dataset and I am trying to create the 4th

Report Month	Customer	Status	Prior Month Status
Jan	ABC	Red
Feb	ABC	Red	Red
March	ABC	Yellow	Red
Apr	ABC	Yellow	Yellow
May	ABC	Green	Yellow
Jan	DEF	Red
Feb	DEF	Yellow	Red
May	DEF	Green	Yellow
Jan	GHI	Red
March	GHI	Yellow	Red
May	GHI	Green	Yellow

[tamerj1]

Hi @Anonymous 

month is text or date data type?

[Anonymous]

it's date type

[tamerj1 · Accepted solution]

@Anonymous 

Please try

Prior Month Status =
MAXX (
TOPN (
1,
FILTER (
CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[Customer] ) ),
'Table'[Month] < EARLIER ( 'Table'[Month] )
),
'Table'[Month]
),
'Table'[Status]
)

[Anonymous]

omg this worked THANK YOU