Log In
Register
Home
New Member
October 26, 2017
Offline
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.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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.
1 Guest(s)
