Replace NULL value for a measure with previous NOT NULL value in Matrix view

[ArulselvanD07]

I am having a scenario where I want to replace null values for a measure with previous not null value available on that row in Matrix view. If the first element of the row itself is NULL, I would like to populate zero for that. 

Snaphost of my matrix is provided below

In the example provided below, for 2017 09, 1st value is NULL, so I woud like to popualte 0 there. For 2017 06, 4th value is NULL, I would like to populate the previous not null value on that row for that [which is 2.281. Is there any option available in Power BI to achieve this functionality.

Thanks for the help.

[Andrettii]

Maybe this can help

For a matrix

https://community.fabric.microsoft.com/t5/Desktop/Return-last-nonblank-value-in-matrix-visual/m-p/31...

For a table

https://www.youtube.com/watch?v=_PyMDc42hgs

[Greg_Deckler]

What does your value look like? Is it a column or a measure? If measure, what is your formula?

[ArulselvanD07]

Hi @Greg_Deckler, 

It is a measure. Formula is provided below for my measure: 

Measure = ([Running Count]/[Build Volume]) * 100

[ArulselvanD07]

both Running Count and build volume are measures. They are not a physical columns.

[Greg_Deckler]

OK, can you post your measure formulas?

[ArulselvanD07]

Hi @Greg_Deckler 

Please find below the formula for the measures I am having

Measure = ([Running Count]/[Build Volume]) * 100

Running Count = CALCULATE(count('Fact1'[ProductNo]),FILTER(ALL('Fact1'),'Fact1'[Mnth Aft Build] <= MAX('Fact1'[Mnth Aft Build])),VALUES('Fact1'[Build Month]))

Build Volume = COUNT('Fact2'[ProductNo])

[Greg_Deckler]

Tough without any sample data to confirm, but it seems like you could create these three measures:

Previous Running Count = 
CALCULATE(
	COUNT('Fact1'[ProductNo]),
	FILTER(
		ALL('Fact1'),
		'Fact1'[Mnth Aft Build] <= MAX('Fact1'[Mnth Aft Build])-1
	),
	VALUES('Fact1'[Build Month]))


Previous Measure = [Previous Running Count]/[Build Volume] * 100

Matrix Measure = IF(ISBLANK([Measure]),IF([Mnth Aft Build]=1,0,[Previous Measure]),[Measure])

Use Matrix Measure in your matrix.

[ArulselvanD07]

Hi @Greg_Deckler,

I am getting an error for the third measure, error says: The value for 'Mnth Aft Build' cannot be determined. Either 'Mnth Aft Build' doesn't exist, or there is no current row for a column named 'Mnth Aft Build'. Am I missing something here? Kindly clarify. Thanks

[Greg_Deckler]

Sorry, this is why I like mocking things up with data, small semantic/syntax error:

Matrix Measure = IF(ISBLANK([Measure]),IF(MAX([Mnth Aft Build])=1,0,[Previous Measure]),[Measure])

[ArulselvanD07]

Hi @Greg_Deckler

Thanks for your reply.

Formula is working fine, however I noticed that there is no data avaialble for few month after build values. so those elements are still populated as blank in the Matrix view. I have attached my pbix file for your reference.

Please let me know if the scenarios like this can be handled? Thanks for your suggestions and guidance.

https://1drv.ms/u/s!ArlDJvgV4lGOaZb01vLSbYOEcnc

[v-yuezhe-msft]

@ArulselvanD07,

Please check DAX in the PBIX file below.
https://1drv.ms/u/s!AhsotbnGu1Nok1QypiHrRnSnrCTF


Regards,
Lydia

[ArulselvanD07]

Hi @v-yuezhe-msft,

Thanks for your comments and the modified file. 

It works perfectly fine when the first element of the row is blank and when there is a blank value in between. However it is also getting expanded to all available cells of the matrix. I should have mentioned this earlier, my apologies. Matrix values should be populated only till the end of maximum month after build value available for that build month.

In this case, for build month 2017 09, Matrix should be populated till the Month after build value 7, as  the maximum month after build value for 2017 09 is 7. Is there any option available to exclude the values that go beyond maximum month after build values? Thanks in advance for your suggestions and guidance.

[ArulselvanD07]

I have tried to create a new calcualted table with build month, mix month after build and max month after build, cross joined that with the 'Table' [which has all Month after build values] and tried to build the measure on top of the newly created calculated table, but the approach is not working.