Fill in missing values in a Matrix

[Anonymous]

I have a matrix pulling from a table with incomplete data. A record is created when the value changes. If the value stays the same between years, there's no record. If a given year is newer than the change year, I want the value of the column to be the last valid entry for that item. 

Rows is Item No
Column is Year
Value is a measure that's supposed to fill in Empty Values: 

Latest Lead Time =

VAR CurrentYear = YEAR(MAX('lead time history'[Year]))

RETURN

    IF(

        ISBLANK(MAX('lead time history'[Clean New Lead Time])),

        CALCULATE(

            LASTNONBLANK('lead time history'[Clean New Lead Time], 0),

            FILTER(

                ALL('lead time history'),

                'lead time history'[item no] = SELECTEDVALUE('lead time history'[item no]) &&

                'lead time history'[Year] <= CurrentYear

            )

        ),

        MAX('lead time history'[Clean New Lead Time])

    )

What I have

What I want:

Lead Time History table:

[Anonymous · Accepted solution]

Hi @Anonymous ,

first we need create a table:

Table = GENERATESERIES(MIN('lead time history'[Year]),MAX('lead time history'[Year]),1)

then create a measure:

Measure = 
VAR CurrentItem = MAX('lead time history'[Item No])
VAR CurrentYear = MAX('Table'[Year])
RETURN
    CALCULATE(
        MAX('lead time history'[Clean New Lead Time]),
        FILTER(
            ALL('lead time history'),
            'lead time history'[Item No] = CurrentItem
            && 'lead time history'[Year] <= CurrentYear
        )
    )

model:

result:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[devanshi]

VAR curritems = [Item No]
VAR curryear = [Year]
VAR ChangeLastyear = CALCULATE (
                                                               MAX([Year]),
                                                                ALLEXCEPTED('TableName', [Item No],[Year] <= curryear)
                                                         )
RETURN
      CALCULATE (
                                  LASTNONBLANK([Value],1),
                                  ALLEXCEPTED('TableName', [Item No],[Year] <= ChangeLastyear)
                           )

[Anonymous]

That calculation isn't giving me the right answers. For example: 
Matrix:

Data source