Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Fill empty rows/values with previous/last measure

Hi All,

 

I am really at a loss on this one. My dataset has a reciept date column, I also have a Calendar table to bring in all dates within a month. Bascially what I want to do is make sure there are values for all dates within the year (not just the reciept dates). If there is no value for a specific row, I want it to bring in the last date value. 

 

I tried to do the below and it is ALMOST what I want but not quite. 

 

LastNonBlank Value =
var LastNonBlankDate=CALCULATE(MAX('Calender'[Date]),FILTER(ALL('Calender'),Calender[Date]<=MAX('Calender'[Date]) && 'Master PPV Table'[Average of Actual]<>0))
return
CALCULATE([Average of Actual],
    FILTER(ALL('Calender'),Calender[Date]=LastNonBlankDate))
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ，

    Assume that the field [Average of Actual] is a fact field not measure, you can create a measure as below. Then create a visual with this setting( X axis: [Date] of table Calender   Value: New created measure [Average Actual values]).

    Average Actual values =
VAR Curdate =
    MAX ( 'Calender'[Date] )
VAR LastNonBlankDate =
    CALCULATE (
        MAX ( 'Master PPV Table'[Date] ),
        FILTER (
            ALL ( 'Master PPV Table' ),
            'Master PPV Table'[Date] < Curdate
                && NOT ( ISBLANK ( 'Master PPV Table'[Average of Actual] ) )
        )
    )
RETURN
    IF (
        ISBLANK ( SUM ( 'Master PPV Table'[Average of Actual] ) ),
        CALCULATE (
            [Average of Actual],
            FILTER (
                ALL ( 'Master PPV Table' ),
                'Master PPV Table'[Date] = LastNonBlankDate
            )
        ),
        SUM ( 'Master PPV Table'[Average of Actual] )
    )

    If the above one can't get the correct result, please provide some sample data and the formula of measure if it involve any measure.

    Best Regards

    Rena

     

     

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    You seem to be reinventing the LASTNONBLANKVALUE() function.

     

    Technically you can do the same in Power Query via Fill..Up.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ，

    Assume that the field [Average of Actual] is a fact field not measure, you can create a measure as below. Then create a visual with this setting( X axis: [Date] of table Calender   Value: New created measure [Average Actual values]).

    Average Actual values =
VAR Curdate =
    MAX ( 'Calender'[Date] )
VAR LastNonBlankDate =
    CALCULATE (
        MAX ( 'Master PPV Table'[Date] ),
        FILTER (
            ALL ( 'Master PPV Table' ),
            'Master PPV Table'[Date] < Curdate
                && NOT ( ISBLANK ( 'Master PPV Table'[Average of Actual] ) )
        )
    )
RETURN
    IF (
        ISBLANK ( SUM ( 'Master PPV Table'[Average of Actual] ) ),
        CALCULATE (
            [Average of Actual],
            FILTER (
                ALL ( 'Master PPV Table' ),
                'Master PPV Table'[Date] = LastNonBlankDate
            )
        ),
        SUM ( 'Master PPV Table'[Average of Actual] )
    )

    If the above one can't get the correct result, please provide some sample data and the formula of measure if it involve any measure.

    Best Regards

    Rena