cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors