Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Fill in missing values in a Matrix

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 ...
Matrix Visual
  • Anonymous's avatar
    Anonymous
    3 years ago

    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's avatar
devanshi
Icon for Helper V rankHelper V
3 years ago

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's avatar
Anonymous
Not applicable
3 years ago

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

 

Data source