topic Modification of Dax to show 1 only once after depreciation reaches zero or below. in DAX Commands and Tips

Modification of Dax to show 1 only once after depreciation reaches zero or below.

YogeshWaran2010 — Mon, 23 Dec 2024 06:58:14 GMT

I am calculating rolling depreciation based on the last available book value (Machine valuation after depreciation). I have fixed depreciation every month. The following dax is working as expected. The issue is when the depreciation becomes negative I am completely filtering it. But I want to show '1' as the Book value and stop from next month.
Eg:

Input data:

Machine	Acquisition amount	Depreciation amount	Book value Last month (Oct-2024)	date
Machine 1	100,000	20,500	60,000	2024-11-01

Output:

Machine	Acquisition amount	Depreciation amount	Book value	date
Machine 1	100,000	20,500	39,500	2024-12-01
Machine 1	100,000	20,500	19,000	2025-01-01
Machine 1	100,000	20,500	-1,500 (Show 1 and stop)	2025-02-01

Output (What happens if I consider < 1 and an If condition to show 1:

Machine	Acquisition amount	Depreciation amount	Book value	date
Machine 1	100,000	20,500	39,500	2024-12-01
Machine 1	100,000	20,500	19,000	2025-01-01
Machine 1	100,000	20,500	1	2025-02-01
Machine 1	100,000	20,500	1	2025-03-01
Machine 1	100,000	20,500	1	2025-04-01
....	....	...	....	Continues till calendar table last date

Rolling Book Value = VAR staticDepreciation = CALCULATE(MAX('2024-11-11-converted'[Monthly depreciation amount]), REMOVEFILTERS('Calendar')) -- Fixed monthly depreciation VAR StartDate = CALCULATE(MAX('2024-11-11-converted'[Custom]), REMOVEFILTERS('Calendar')) VAR CurrentDate = MAX('Calendar'[Date]) -- Date in the visual row context VAR InitialBookValue = CALCULATE( MAX('2024-11-11-converted'[Book value at end of previous month]), REMOVEFILTERS('Calendar') -- Remove Calendar table filters ) -- Starting book value VAR MonthsElapsed = DATEDIFF(StartDate, CurrentDate, MONTH) + 1 -- Number of months from start date to current date -- Calculate the Rolling Depreciated Value VAR monthlyDepreciation = MonthsElapsed * staticDepreciation VAR DepreciatedValue = IF( EDATE(StartDate, -1) <= CurrentDate, -- Ensure depreciation only happens from StartDate onward InitialBookValue - monthlyDepreciation, -- Deduct depreciation month-by-month BLANK() -- Return BLANK for rows before the StartDate ) RETURN IF(DepreciatedValue < 1,BLANK(), DepreciatedValue)

Re: Modification of Dax to show 1 only once after depreciation reaches zero or below.

FarhanJeelani — Mon, 23 Dec 2024 08:09:30 GMT

Hi YogeshWaran2010 ,

To address your requirement of showing "1" only once after depreciation becomes negative or zero, you need to modify the DAX formula to ensure that once the book value reaches "1," it stays at "1" for all subsequent periods. This can be achieved by tracking whether the depreciation has reached the threshold and ensuring that subsequent calculations honor this state.

Here’s the modified DAX:

Rolling Book Value = 
VAR staticDepreciation = CALCULATE(MAX('2024-11-11-converted'[Monthly depreciation amount]), REMOVEFILTERS('Calendar'))  -- Fixed monthly depreciation
VAR StartDate = CALCULATE(MAX('2024-11-11-converted'[Custom]), REMOVEFILTERS('Calendar'))
VAR CurrentDate = MAX('Calendar'[Date])                        -- Date in the visual row context
VAR InitialBookValue = CALCULATE(
    MAX('2024-11-11-converted'[Book value at end of previous month]), 
    REMOVEFILTERS('Calendar')  -- Remove Calendar table filters
)  -- Starting book value
VAR MonthsElapsed = DATEDIFF(StartDate, CurrentDate, MONTH) + 1    -- Number of months from start date to current date

-- Calculate the Rolling Depreciated Value
VAR monthlyDepreciation = MonthsElapsed * staticDepreciation
VAR DepreciatedValue = 
    IF(
        EDATE(StartDate, -1) <= CurrentDate,  -- Ensure depreciation only happens from StartDate onward
        InitialBookValue - monthlyDepreciation,  -- Deduct depreciation month-by-month
        BLANK()  -- Return BLANK for rows before the StartDate
    )

-- Check if depreciation has already reached 1 in previous rows
VAR HasReachedOne = 
    CALCULATE(
        MINX(
            FILTER(
                ALL('Calendar'[Date]), 
                'Calendar'[Date] <= CurrentDate && 
                DepreciatedValue <= 1
            ),
            1
        )
    )

-- Return the final value
RETURN
IF(
    HasReachedOne = 1,  -- If "1" has already been reached
    1,                  -- Continue showing "1"
    IF(DepreciatedValue < 1, 1, DepreciatedValue)  -- Otherwise, calculate depreciation as normal
)

Explanation of Changes:

Track If "1" Has Been Reached:
- The HasReachedOne variable checks if the depreciation has already dropped to "1" or below in any previous rows (using the ALL function to bypass row context).
Conditional Logic:
- If HasReachedOne = 1, the measure outputs 1 for the current row and all subsequent rows.
- Otherwise, it calculates the depreciation value as usual, defaulting to "1" when the calculated value drops below "1."
Single "1" Logic:
- This ensures that "1" is displayed only once when the depreciation reaches or drops below zero and prevents recalculating lower values afterward.

Expected Output:

For your example, this logic ensures that the book value transitions as follows:

2024-12-01: 39,500
2025-01-01: 19,000
2025-02-01: 1
2025-03-01 and beyond: 1

This behavior aligns with your requirement to stop recalculating depreciation once the book value reaches "1." Let me know if you need further adjustments!

Please mark this as solution if it helps. Appreciate Kudos.

Re: Modification of Dax to show 1 only once after depreciation reaches zero or below.

YogeshWaran2010 — Mon, 23 Dec 2024 08:59:15 GMT

Hi FarhanJeelani ,

Thank you for your response.

Sorry, I have made a mistake in the required output.

I want the output to check if the values go below 1 then show 1 and stop for consecutive months.

Output:

Machine	Acquisition amount	Depreciation amount	Book value	date
Machine 1	100,000	20,500	39,500	2024-12-01
Machine 1	100,000	20,500	19,000	2025-01-01
Machine 1	100,000	20,500	-1,500 (Show 1 and stop)	2025-02-01

It stops at 2025-02-01 and 2025-03-01 is not printed. But in the shared Dax, consecutive and previous months also print '1'.

Note: Performance of the Dax is very poor after the modification.

Re: Modification of Dax to show 1 only once after depreciation reaches zero or below.

Anonymous — Tue, 24 Dec 2024 02:28:49 GMT

Hi YogeshWaran2010 ,

I create a table as you mentioned.

Then I think you can create a new Calculated Column.

Column = VAR BookValueNegative = CALCULATE ( MIN ( 'Table'[Book value] ), FILTER ( 'Table', 'Table'[Book value] < 0 ) ) RETURN IF ( 'Table'[Book value] < 0 || 'Table'[date] >= CALCULATE ( MIN ( 'Table'[date] ), FILTER ( 'Table', 'Table'[Book value] < 0 ) ), 1, 'Table'[Book value] )

If you want to show things you want, I think you can create a new table.

Table 2 = VAR _FirstOneDate = CALCULATE ( MIN ( 'Table'[date] ), FILTER ( 'Table', 'Table'[Column] = 1 ) ) RETURN FILTER ( 'Table', 'Table'[Column] <> 1 || 'Table'[date] = _FirstOneDate )

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.