Forum Discussion

erdvige's avatar
erdvige
Frequent Visitor
8 years ago
Solved

Using last non-zero value in the same column to populate a specific row's value

Sorry if this has come up before but new user and trying to get my head around some of the functions in PowerBI. I have a series of ~ 20 million sample rows for which I am attributing values based on...
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Hi erdvige

     

    Try adding this calculated Column

     

    New values =
VAR LastNonZeroDate =
    MAXX (
        FILTER ( Table1, Table1[Date] < EARLIER ( Table1[Date] ) && Table1[Value] <> 0 ),
        Table1[Date]
    )
RETURN
    IF (
        Table1[Value] = 0,
        CALCULATE (
            VALUES ( Table1[Value] ),
            FILTER ( ALL ( Table1 ), Table1[Date] = LastNonZeroDate )
        ),
        Table1[Value]
    )
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

Hi erdvige

 

Try adding this calculated Column

 

New values =
VAR LastNonZeroDate =
    MAXX (
        FILTER ( Table1, Table1[Date] < EARLIER ( Table1[Date] ) && Table1[Value] <> 0 ),
        Table1[Date]
    )
RETURN
    IF (
        Table1[Value] = 0,
        CALCULATE (
            VALUES ( Table1[Value] ),
            FILTER ( ALL ( Table1 ), Table1[Date] = LastNonZeroDate )
        ),
        Table1[Value]
    )