Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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 |
_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.
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.