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 using LASTNONBLANK but something isn't working. Here is the case visualized on example:

 

1. I have data structure like this:

 

2. When I pivot it it looks like this:

 

3. My desired result would be like this:

 

This is of course very simplified example on simple Excel data, but it reflects very correctly what I have in mind. One thing I should add is that in Power BI for creating Matrix table I use DimDate as a column.

 

Do you have any suggestions on how the measure/calculated column DAX code should look in this case?

 

Thank in advance for every tips!!

 

Regards,
Karol

Need Help

2 Replies

  • 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