Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

If value is missing insert last non missing value

Hi Power BI Community   I am struggling with something that should be really easy. When there is missing value for specific period I want to insert value from previos non-blank period. I tried usin...
Need Help
v-xicai's avatar
v-xicai
Community Support
6 years ago

Hi Anonymous ,

 

You can create calculated table first, then create column and measure using DAX below. Finally , put measure NonBlankValue onto Values box of Matrix visual.

 

Calculated Table:
Table = CROSSJOIN(DISTINCT(Table1[Item ]),CALENDAR(MIN(Table1[Date]),MAX(Table1[Date])))
 
Column:
Value = CALCULATE(FIRSTNONBLANK(Table1[Value],1),FILTER(ALLSELECTED(Table1),Table1[Item ]='Table'[Item ]&&Table1[Date]='Table'[Date]))
 
Meeasure:
NonBlankValue = var _lastdate=CALCULATE(MAX('Table'[Date]),FILTER(ALLEXCEPT('Table','Table'[Item ]),'Table'[Date]<MAX('Table'[Date])&&[Value]<>BLANK()))
return
IF(MAX('Table'[Value])=BLANK(),CALCULATE(MAX('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[Item ]),'Table'[Date]=_lastdate)),MAX('Table'[Value]))

Result:

 

 

Best Regards,

Amy

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Amy,

     

    Thanks for the tip! It works fine with matrix table, but unforunately it doesn't work that great for charts (see screenshots below - they are coming from acutal data, not dummy one). In the charts I see that values are Also, the other thing is that my dataset is quite huge and I see that it takes a lot of time to process calculated table. Do you know if there is any way to do this inside the table I already have, without having to create calculated table?

     

    This is how I see the result in chart. It shows maximum value in column, not sum of values in column:

     

     

    Is there any easy way to deal with this?

     

    Thanks in advance!

     

    Karol