Forum Discussion

DataVitalizer's avatar
DataVitalizer
Icon for Super User rankSuper User
7 years ago
Solved

DAX: Return the previous nonblankvalue

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.

DAX
DAX Query
Desktop
  • Anonymous's avatar
    Anonymous
    7 years ago
    [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

2 Replies

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