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 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:

 


 

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

3 Replies

  • devanshi's avatar
    devanshi
    Helper 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

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    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