Get Current or Last Non-Zero Value

[JacobK]

I am looking to create a DAX formula that gets the sum of an amount grouped by a category and date. If the sum amount is zero for that category and date then I want to get the last non-zero value.

The current DAX formula that I have works great when only one single category exists, but when I add multiple categories it gets the last non-zero amount across all categories.

Here is the data structure (Left = "DateDim", Right = "AmountFact"):

Very simple model:

Here are the measures:

TotalAmount = SUM(AmountFact[Amount])

CurrentOrLastValue = IF( [TotalAmount] = 0,
    CALCULATE (
        [TotalAmount],
        TOPN(
            1,
            CALCULATETABLE(
                'AmountFact',
                FILTER(
                    ALL('AmountFact'),
                    [TotalAmount] > 0
                ),
                FILTER(
                    ALL( 'DateDim' ),
                    'DateDim'[Date] < max( 'DateDim'[Date] )
                )
            ),
            'AmountFact'[Date],
            DESC
        )
    ),
    [TotalAmount]
)

Here is an example of when only one category exists (Working as expected):

Here is when multiple categories exist:

As you can see above the most recent non-zero value is now the most recent non-zero value of ANY category.

I believe that whats going on is that in the "CurrentOrLastValue" measure when I select the TOPN 1 to get the most recent non-zero record it is looking at ALL the records. I did a bunch research looking at how I could filter that CALCULATEDTABLE to only get the category of the current record without any luck. Hoping that someone can help me out here!

[tamerj1 · Accepted solution]

Hi @JacobK 
You may try 

CurrentOrLastValue =
VAR CurrentDate =
    MAX ( 'DateDim'[Date] )
VAR CurrentAmount = [TotalAmount]
VAR CurrentCategoryTable =
    CALCULATETABLE (
        'AmountFact',
        ALLEXCEPT ( 'AmountFact', 'AmountFact'[Category] )
    )
VAR CurrentCategoryWithValueTable =
    FILTER ( CurrentCategoryTable, [TotalAmount] > 0 )
VAR PreviousDateWithValue =
    TOPN (
        1,
        FILTER ( CurrentCategoryWithValueTable, 'DateDim'[Date] < CurrentDate ),
        'AmountFact'[Date], DESC
    )
VAR PreviousAmount =
    CALCULATE ( [TotalAmount], PreviousDateWithValue )
RETURN
    IF ( CurrentAmount = 0, PreviousAmount, CurrentAmount )