I have a table included customers. The customers got a discount to some days. There are rows for customer and date without discount value (NULL / BLANK). For this entries should valid the last discount. I am looking for a DAX function to do that - if the discount is empty then look for the last not empty discount in the customer timeline. I tried LASTNONBLANK() but without success.
Thank you for help.
Hi,
You can try this function:
=
IF (
ISBLANK ( Table1[discount] ),
CALCULATE (
MAX ( table1[discount] ),
FILTER ( ALL ( table1 ), table1[customer] = EARLIER ( Table1[customer] ) ),
table1[date] < EARLIER ( Table1[date] )
),
Table1[discount]
)
Please test it with real data and check the results, in some scenarios it may return wrong results (because of the MAX function, if the discount is decreasing for the same client). If this will not happen, then it will return the expected values.
