I want to find Acquisition and Depreciation using Power bi Dax

[amal_01]

I want to calculate Acquisition prior Years , and Acquisition This Year and Disposals at acquisition this year , also I want calculate 
Depreciation Prior Years ,Depreciation and value adjustment this year and Disposals at Depreciation this Year.

I have Two Tables AssetsTrans has relationship with AssetGroupName (building, cars, furntiure as so on).
The columns in AssetsTransTable are TransType and TransAmount , TransDate
I have Used this measure to calculate the  Acquisition prior Years

CALCULATE(
    SUM(AssetsTransEntity[AmountMST]),
    ALLSELECTED(AssetsTransEntity[TransDate].[Date]),
        FILTER(
        AssetsTransEntity,
        AssetsTransEntity[TRANSTYPE] = "Acquisition" || AssetsTransEntity[TRANSTYPE] = "AcquisitionAdj"
        )
)

the total amount of  Acquisition prior Years is clear If my slicer  selected all years Or used before date (slicer setting option ) what I meant If I selected 2019 It mean calculate  all years before 2019 and unitl the end of 2019

, but also I want to calculate Acquisition This Year and Disposals at acquisition this year these are to measures. 

Acquisition This Year 

CALCULATE(
    SUM(AssetsTransEntity[AmountMST]),
        FILTER(
        AssetsTransEntity,
        AssetsTransEntity[TRANSTYPE] = "Acquisition" || AssetsTransEntity[TransType] = "AcquisitionAdj"
    ),
    FILTER(
        AssetsTransEntity,
        AssetsTransEntity[AmountMST] > 0 
    )
)

 Disposals at acquisition this year

CALCULATE(
    SUM(AssetsTransEntity[AmountMST]),
    FILTER(
        ALL(AssetsTransEntity[TransDate].[Date]),
        'AssetsTransEntity'[TransDate].[Date] < MAX('AssetsTransEntity'[TransDate].[Date])
    ),
        FILTER(
        AssetsTransEntity,
        AssetsTransEntity[TRANSTYPE] = "AcqPriorYears" || AssetsTransEntity[TransType] = "AcqAdjPriorYears"
    )
)

these two measure are calculeted very well If I chooes date between (1/1/2019)to(12/31/2019) or between any years,

but  Acquisition prior Years amount does not clear in this case  it becomes like Acquisition This Year amount If I choose slicer between why ?

what I want! Acquisition prior Years calculate all amount unit 2019 and Acquisition This Year and Disposals at acquisition this year just calulate the amount in selected years 2019 or any years just between.

same thing with Depreciation Prior Years ,Depreciation and value adjustment this year and Disposals at Depreciation this Year

these are measures 
Depreciation Prior Years

CALCULATE(
    SUM(AssetsTransEntity[AmountMST]),
    ALLSELECTED(AssetsTransEntity[TransDate].[Date]),
        FILTER(
        AssetsTransEntity,
        AssetsTransEntity[TRANSTYPE] = "Depreciation" || AssetsTransEntity[TRANSTYPE] = "DepreciationAdj"
        )
)

Depreciation and value adjustment this year

CALCULATE(
    SUM(AssetsTransEntity[AmountMST]),
    ALLSELECTED(AssetsTransEntity[TransDate].[Date]),
        FILTER(
        AssetsTransEntity,
        AssetsTransEntity[TRANSTYPE] = "Depreciation" || AssetsTransEntity[TRANSTYPE] = "DepreciationAdj"
        ),
        FILTER(
        AssetsTransEntity,
        AssetsTransEntity[AMOUNTMST] > 0
    )
)

 Disposals at Depreciation this Year

CALCULATE(
    SUM(AssetsTransEntity[AmountMST]),
    ALLSELECTED(AssetsTransEntity[TransDate].[Date]),
        FILTER(
        AssetsTransEntity,
        AssetsTransEntity[TRANSTYPE] = "Depreciation" 
        ),
        FILTER(
        AssetsTransEntity,
        AssetsTransEntity[AMOUNTMST] < 0
    )
)

is my measures are not correct or whare is the problem  ?

[Anonymous]

Hi @amal_01 ,

Based on the description, try to add the formula to filter the date range for Acquisition prior years and Depreciation Prior Years.

Acquisition prior years =
CALCULATE(
    SUM(AssetsTransEntity[AmountMST]),
    FILTER(
        ALLSELECTED(AssetsTransEntity[TransDate]),
        Year(AssetsTransEntity[TransDate]) < YEAR(MAX(AssetsTransEntity[TransDate]))
    ),
        FILTER(
        AssetsTransEntity,
        AssetsTransEntity[TRANSTYPE] = "Acquisition" || AssetsTransEntity[TRANSTYPE] = "AcquisitionAdj"
        )
)

ALLSELECTED function (DAX) - DAX | Microsoft Learn

Best Regards,

Wisdom Wu

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