Forum Discussion
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 tryCurrentOrLastValue = 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
- tamerj1Community Champion
Hi JacobK
You may tryCurrentOrLastValue = 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 )