filling blanks with previous row in matrix visual

[sajal161292]

Hi,

I have a report as shown below.

As shown there is no data from 2018-01 till 2018-11 for second hieracrchy.

In case of any blank,I want to replace it with the data in previous row and extend it till last week so that there is no blank row and there exists data across every week

[v-yuezhe-msft · Accepted solution]

@sajal161292,

I make a test in the following sample table.


First, add a conditional column in the table.

NewValue = IF(ISBLANK(Table[Value]), CALCULATE(FIRSTNONBLANK(Table[Value],Table[Value]),FILTER(Table,Table[Custom]=EARLIER(Table[Custom]) && Table[Week]=EARLIER(Table[Week]))),Table[Value])

Second, create a new columns using DAX below.


Third, create Matrix visual as below.


Regards,
Lydia

[sajal161292]

Hi,

It is not working correctly for me.

I think you have got confused with the requirement.

Purpose is to fill the weeks having no data as showin in the screenshots with the data of previous week and roll it up across the weeks till there is data for the week.

So can you please suggest an alternative?

[v-yuezhe-msft]

@sajal161292,

Please share sample data of your original table.

Regards,
Lydia

[sajal161292]

Hi,

Please find the sample file in the link below:

https://drive.google.com/open?id=1Yxc6EX0f7pRoAK_HmlDUwjgS9v37r2LM

[v-yuezhe-msft]

@sajal161292,

In your sample data, I note that you don't have rows in some weeks for specific items, in this case, I am afraid that it is not possible to fill blanks with previous row in Matrix visual.

Regards,
Lydia

[sajal161292]

Hi,

I need to implement it urgently in one of my report.

Is there any alternative approach to achieve this?

[v-yuezhe-msft]

@sajal161292,

You can add rows for these items which have blank values in these weeks(2018-01, 2018-02.....),  then create the DAX I provide in your table. And the  sample table has been provided in my first reply.

Regards,
Lydia

[sajal161292]

Hi,

How can i add blank rows in matrix visual in power BI.

Or do i need to add blank rows in SQL query?

[v-yuezhe-msft]

@sajal161292,

You would need to add these rows in your original data source but not in Martix.

Regards,

Lydia

[sajal161292]

Hi,

When i have to add blank rows in my SQL query i need to hardcode the quantity for a particular date right?

[v-yuezhe-msft]

@sajal161292,

Yes. Add null values for quantity, for more details, please see the Value column in my first reply.

Regards,
Lydia