Previous month value if current month value is blank - unable to reply

[HufferD]

I was unable to reply to your initial message, @Vladas, so I’m picking up the thread from Previous month value if current month value is blank here:

@Vladas

Hi, I want to show the average price for the month, and if there are no prices, then it should show the average price of the previous month. What would be the best practice to achieve this?

Your measure:

Avg Monthly Price

Avg Monthly Price = 
VAR SelectedMonth = SELECTEDVALUE('Date'[Date])
VAR PricesInMonth = 
    FILTER(
        'PriceList',
        SelectedMonth >= 'PriceList'[FromDate] &&
        SelectedMonth <= 'PriceList'[ToDate]
    )
RETURN
IF(ISBLANK(SUMX(PricesInMonth, 'PriceList'[Price])),
    CALCULATE(AVERAGE('PriceList'[Price]), DATEADD('Date'[Date], -1, MONTH)),
    AVERAGE('PriceList'[Price])
)

is concise, but it may not handle consecutive months without prices effectively. To illustrate, if both January and February lack prices, the February data would simply reflect January’s (also missing) value.

A more robust solution might be to recursively check previous months until a non-blank price is encountered:

Revised measure

Avg Monthly Price = 
VAR SelectedMonth = SELECTEDVALUE('Date'[Date])
VAR PricesInMonth = 
    FILTER(
        'PriceList',
        SelectedMonth >= 'PriceList'[FromDate] &&
        SelectedMonth <= 'PriceList'[ToDate]
    )
VAR CurrentMonthPrice = AVERAGE('PriceList'[Price])

VAR PreviousMonthPrice = 
    CALCULATE(
        AVERAGE('PriceList'[Price]),
        FILTER(
            ALL('Date'[Date]),
            'Date'[Date] < SelectedMonth && 
            NOT(ISBLANK(CALCULATE(AVERAGE('PriceList'[Price]), DATEADD('Date'[Date], -1, MONTH))))
        ),
        LASTNONBLANK('Date'[Date], AVERAGE('PriceList'[Price]))
    )
RETURN
IF(ISBLANK(CurrentMonthPrice),
    PreviousMonthPrice,
    CurrentMonthPrice
)

This revision leverages LASTNONBLANK() to get the latest date with a non-blank average price. The subsequent FILTER() ensures that even with successive months of missing prices, a valid preceding month is selected.

However, the crux of your question revolves around the suitability of the imputation method. In the context of monthly average prices, carrying forward the last known value (as your initial formula suggests) seems appropriate, especially if price shifts aren’t drastic. If prices are volatile, you might consider interpolation or advanced imputation.

Attractive alternatives include a) Interpolating missing values by averaging the nearest known data points both preceding and succeeding the absent data or b) using statistical models to predict missing values based on other, available data.

hth