cancel
Showing results 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

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 Name Milestone Date Money At Risk Plant 1 1 03/23/21 \$7,000.00 Plant 2 2 04/30/21 \$                        - Plant 1 3 11/23/21 \$11,591.80 Plant 1 4 4/4/2022 \$184,591.80 Plant 1 5 03/01/23 \$14,184,591.80 Plant 2 6 04/07/23 \$      173,000.00 Plant 2 7 06/28/23 \$      885,000.00 Plant 1 8 09/07/23 \$14,264,591.80 Plant 2 9 06/10/24 \$   3,090,000.00

Here is what my matrix looks like currently in PBI:

 Date Plant 1 Plant 2 Grand 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:

 Date Plant 1 Plant 2 Grand 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
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)

Frequent Visitor

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.

Announcements

#### 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 Monthly Update - June 2024

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

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors