Forum Discussion

JacobK's avatar
JacobK
Regular Visitor
4 years ago
Solved

Get Current or Last Non-Zero Value

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!

  • 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 )

1 Reply

  • tamerj1's avatar
    tamerj1
    Community Champion

    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 )