Forum Discussion
Fill Blanks with latest if based on sequence
Hello there,
I'm having some trouble on solving this one.
To give a breif example, we have a sequence from 1 - 5 each with a status and date. Sometimes the user skips this sequence and now I'd like to fill the gap with the latest date available.
Example
| ID | Status | StatusID | LatestDate |
| 025755 | Intial Lead | 1 | Jan 1 2021 |
| 025755 | Regular Lead | 2 | Jan 5 2021 |
| 025755 | Hot Lead | 3 | To be filled |
| 025755 | Signature Pending | 4 | To be filled |
| 025755 | Success | 5 | Jan 25 2021 |
Example2
| ID | Status | StatusID | LatestDate |
| 025755 | Intial Lead | 1 | Jan 1 2021 |
| 025755 | Regular Lead | 2 | To be filled |
| 025755 | Hot Lead | 3 | 14 Jan 2021 |
| 025755 | Signature Pending | 4 | |
| 025755 | Success | 5 |
Now as you can see we have 2 statuses in example 1 that were skipped and thus have no dates. My objective is to fill these blank spaces for each group(ID) with the latest date available based on the max status/statusID.
In example 2, 1 status was skipped and the latest current Id is 3, so we only need to fill the one in between.
By default, status(ID) 1 always has a date.
Thanks!
Anonymous , a new column
column =
var _max = maxx(filter(Table,[ID] = earlier([ID]) && not(isblank([Latest Date])) ) ,[Latest Date])
return
if(isblank([Latest Date]), _max, [Latest Date])
2 Replies
- amitchandak
Super User
Anonymous , a new column
column =
var _max = maxx(filter(Table,[ID] = earlier([ID]) && not(isblank([Latest Date])) ) ,[Latest Date])
return
if(isblank([Latest Date]), _max, [Latest Date])
Hi there amitchandak
Thank you so much, this actually worked to do what I require. I'm still new to PBI, and still finding my way around working with row context.
Could you breifly explain the logic of compare with earlier ID?