I have a Power BI report and I want to find the last date some action took place that had a non-0 value. In the following example:
|-------------|------------|
| ActionDate | ActionAmt|
|-------------|------------|
| 1-Feb | -100 |
|-------------|------------|
| 1-Feb | 100 |
|-------------|------------|
| 10-Jan | 150 |
|-------------|------------|
I want to return the 10-Jan date, not 1-Feb, since 10-Jan is the first non-0 value summed by ActionDay. My code returns 1-Feb:
Last Action Date =
VAR maxdatekey =
CALCULATE ( MAX ( 'Action'[ActionDateKey] ), 'Action'[ActionAmount] > 0 )
RETURN
IF (
maxdatekey,
FORMAT (
LOOKUPVALUE ( 'Date'[DateValue], 'Date'[DateKey], maxdatekey ),
"dd-MMM-yyyy"
)
)
How do I further group this to exclude the summarized 0 days?