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.  ...
Calculatetable
DAX
TopN
  • tamerj1's avatar
    tamerj1
    4 years ago

    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 )
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

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 )