Forum Discussion
How to show prior entry when month missing
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 |
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]
)
4 Replies
- tamerj1
Community Champion
Hi Anonymous
month is text or date data type?
- tamerj1
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]
)