Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
aejohnson04
Frequent Visitor

Help filling in blanks with previous value

Hi all,

 

I'm trying to create a matrix in PBI that fills the rows with no data with the previous value for that category. I'd like to use a DAX function to do this if possible. I've tried to use other forum answers, and nothing seems to populate the correct values. 

 

Here is my original data table:

Project NameMilestoneDateMoney At Risk
Plant 1103/23/21$7,000.00
Plant 2204/30/21 $                        -  
Plant 1311/23/21$11,591.80
Plant 144/4/2022$184,591.80
Plant 1503/01/23$14,184,591.80
Plant 2604/07/23 $      173,000.00
Plant 2706/28/23 $      885,000.00
Plant 1809/07/23$14,264,591.80
Plant 2906/10/24 $   3,090,000.00

 

Here is what my matrix looks like currently in PBI:

DatePlant 1Plant 2Grand Total
03/23/21 $             7,000.00  $             7,000.00
04/30/21  $                       -   $                          -  
11/23/21 $          11,591.80  $          11,591.80
04/04/22 $        184,591.80  $        184,591.80
03/01/23 $  14,184,591.80  $  14,184,591.80
04/07/23  $      173,000.00 $        173,000.00
06/28/23  $      885,000.00 $        885,000.00
09/07/23 $  14,264,591.80  $  14,264,591.80
06/10/24  $  3,090,000.00 $    3,090,000.00

 

Here is how I am trying to get the table to look like with the DAX measure:

 

DatePlant 1Plant 2Grand Total
03/23/21 $             7,000.00 $                       -   $             7,000.00
04/30/21 $             7,000.00 $                       -   $             7,000.00
11/23/21 $          11,591.80 $                       -   $          11,591.80
04/04/22 $        184,591.80 $                       -   $        184,591.80
03/01/23 $  14,184,591.80 $                       -   $  14,184,591.80
04/07/23 $  14,184,591.80 $      173,000.00 $  14,357,591.80
06/28/23 $  14,184,591.80 $      885,000.00 $  15,069,591.80
09/07/23 $  14,264,591.80 $      885,000.00 $  15,149,591.80
06/10/24 $  14,264,591.80 $  3,090,000.00 $  17,354,591.80
2 REPLIES 2
bsheffer
Continued Contributor
Continued Contributor

_test =
var _date = max('table'[date]) -- implicit row filter should return the value for the row
var _project = max('table'[project]) -- matrix should restrict this to the correct value for the column
var _current_value = max('table'[Money At Risk]) -- current value of the cell
var _last_good_value_row = calculate(max('table'[Milestone]), filter(all('table'), 'table'[Money At Risk] > 0 && 'table'[project] = _project && 'table'[date] <= _date)) -- gets the last milestone for the project with a good value
var _last_good_value = calculate(max('table'[Money At Risk]), filter(all('table'), 'table'[milestone] = _last_good_value_row)) -- gets that value
return
if(_current_value > 0, _current_value, _last_good_value)

Thanks for the response. Your logic makes sense, but it is still returning the table I am also getting with the blank values. I tried to adjust the return statement to  if(ISBLANK(_current_value), BLANK(), _last_good_value) , but I still get the same table. Any thoughts? I believe it is due to the fact that the original table does not have values for every date for each project. Example - Plant 1 has data for 3/23/21, but Plant 2 does not. I'm not sure how to work around this.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors