Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Fill in previous values

I need to fill in values for each week based on previous values. Nothing I've tried is working. I have to use DAX, no Power Query. I have fields for Entity, WeekEnd Date, and Status. The matrix loo...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Unpivoted would look like this:

EntityWeekEndStatus
EntityAJun 5Pending Approval
EntityAMay 29 
EntityAMay 22 
EntityAMay 15In Progress
EntityAMay 8Pending Approval
EntityAMay 1Ready
EntityAApr 24Needs Assignment

 

Does that help with figuring out the DAX?

FrankAT's avatar
FrankAT
Icon for Community Champion rankCommunity Champion
6 years ago

Hi Anonymous,

the unpivoted sample data looks like that:

 

 

But it's not clear what's the rule to fill in the missing values?

Regards FrankAT

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    FrankAT  It's probably easier to see what I want in the linked PBIX.

     

    Switch your sort on the unpivoted data like I did, sorting on Entity instead of date.

     

    What I want is: for each date, if an entity has a status, use that status. If it doesn't have a status, look back to the last non blank status (or date?) and use that.

     

    So for Entity A, on May 22 there is no status so it needs to look back to May 15 to find the status to use.