Forum Discussion
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 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!
9 Replies
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
Why do you have to use DAX? PowerQuery is the place to fix this is, it would be hard to do in DAX.
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.
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 measureMost 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 resultIf this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
