Fill in previous values

[Anonymous]

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 looks like:

WeekEnd	EntityA	EntityB	EntityC
June 5	Pending Approval
May 29		In Progress
May 22		Pending Approval
May 15	In Progress	Ready
May 8	Pending Approval		Needs Assignment
May 1	Ready
Apr 24	Needs Assignment

What I need is to fill in those blanks:

WeekEnd	EntityA	EntityB	EntityC
June 5	Pending Approval	In Progress	Needs Assignment
May 29	In Progress	In Progress	Needs Assignment
May 22	In Progress	Pending Approval	Needs Assignment
May 15	In Progress	Ready	Needs Assignment
May 8	Pending Approval		Needs Assignment
May 1	Ready
Apr 24	Needs Assignment

My PBIX file is linked and has more specific info.

I have tried the following and none of these work:

https://community.powerbi.com/t5/Desktop/Fill-blanks-with-previous-value/m-p/492501#M229520

https://community.powerbi.com/t5/Desktop/Fill-blank-values-with-previous-value/m-p/1210628#M539887

PLEASE refer to my PBIX file - everything I've tried gives circular dependencies or other problems so there's something about how my data is set up. Thanks for your help!

[mahoneypat]

Here is one way to solve this.  Please do these steps:

1.  Make a Date table - you need one if you want to see status now for weeks that don't exist in your data for those entities.  I made this simple one, but much more is possible.  Also, you should turn off Auto Date/Time in this file (and all future files IMO).

Date = ADDCOLUMNS(CALENDAR(MIN(Events[Occurred]), TODAY()),

"WeekStart", [Date] - WEEKDAY([Date]))

2.  Add a Date table column to your Events table to make a column that you can relate to the new Date table.

Date = DATE(YEAR(Events[Occurred]), MONTH(Events[Occurred]), DAY(Events[Occurred]))

3.  Make a relationship between the Date[Date] and Events[Date] columns.

4.  Make this measure 

Most Recent Status = var thisweekmax = MAX('Date'[Date])

return CALCULATE(MIN(Events[Status of Event]), ALL('Date'), 'Date'[Date]<=thisweekmax)

5.  Use that matrix in your matrix.  And use the WeekStart column from your Date table for the columns (and keep Entity in the rows).

You will get this result

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Unfortunately this didn't work. I'm still trying to find a solution.

[FrankAT]

Hi @Anonymous,

I agree with @jeroenterheerdt. Power Query is the right place to do this job. EntityA - EntityC must be unpivoted and filled with the missing values.

Regards FrankAT

[jeroenterheerdt]

Why do you have to use DAX? PowerQuery is the place to fix this is, it would be hard to do in DAX.

[Anonymous]

I'm on a direct query to the server, I can't do any transforms in Power Query.

[jeroenterheerdt]

Hmm, then I am sorry but cannot be of much help. The good news is that that behavior might change soon depending on which source you are using of course.

[Anonymous]

Unpivoted would look like this:

Entity	WeekEnd	Status
EntityA	Jun 5	Pending Approval
EntityA	May 29
EntityA	May 22
EntityA	May 15	In Progress
EntityA	May 8	Pending Approval
EntityA	May 1	Ready
EntityA	Apr 24	Needs Assignment

Does that help with figuring out the DAX?

[FrankAT]

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]

@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.