DAX: Return the previous nonblankvalue

[DataVitalizer]

Hi Community,

I am working on a sales table (1), when the sales value is null I have to return the previous nonblankvalue for each product (2).

I used the following formula but it's not returning the values I am looking for:

DAX_Column = IF(SUM('Table'[Sales])=BLANK();CALCULATE(SUM('Table'[Sales]);PREVIOUSDAY('Table'[DateKey]));SUM('Table'[Sales]))

Could you please help me correcting my formula to get the green column.

Thank you in advance.

[Anonymous · Accepted solution]

[Column] =
var __product = Products[Product]
var __date = __Products[DateKey]
var __lookupTable =
	filter(
		filter(
			Products,
			Products[Product] = __product
		),
		NOT ISBLANK( Products[Sales] )
		&& Products[DateKey] <= __date 
	)
var __sales =
	MAXX(
		TOPN(
			1,
			__lookupTable,
			Products[DateKey]
		),
		Products[Sales]
	)
return
	__sales

Best

Darek

[DataVitalizer]

Hi @Anonymous ,

Thank you for the solution. It works 🙂