Calculate sum over previous years

[BHarm65]

Hi,

I have a question about a date calculation.

I have a report that needs to calculate the values of fixed assets.

The reports need to display the selected year for certain columns and for other columns the totals unit the selected year.

See example below

The columns Additions, Depreciation and Disposal display the values from  the selected year.

But the column Acquisitions prior years needs to display the value of the Aqcuisition prior to the selected year.

In the example I selected 2022 and need to know the Acquisition value until 2021

I created a measure but it still respond to the date slices.

I also tried a measure to disgard the date slicer but it will give me all the values, not until 2021

Acquisitions prio years =

CALCULATE (

    SUM ( faLedgerEntry[amount] ),

    faLedgerEntry[faPostingType] = "Acquisition Cost",

    ALL ('Calendar'[Year]))

I also extracted the year from the date slicer and add it to a filter in a measure, but it still gives me the selected year.

Selected Year =

CALCULATE (

    MAX( 'Calendar'[Year] )

)

Has anyone a suggestion for me how to calculate the value of the prior years?

Many thanks in advance

[v-tianyich-msft · Accepted solution]

Hi @BHarm65 ,

I made simple samples and you can check the results below:

You just need to add conditions to the fiter according to your needs.

Measure = var _select= SELECTEDVALUE(DimDate[Year])
return SUMX(FILTER('Table',[Date].[Year]<_select),[Value])

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[Fowmy]

@BHarm65 

Use the following measrue:

Acquisitions prio years =
VAR __SelectedYear =  MAX( 'Calendar'[Year] )
RETURN
CALCULATE (
    SUM ( faLedgerEntry[amount] ),
    faLedgerEntry[faPostingType] = "Acquisition Cost",
    'Calendar'[Year] < __SelectedYear
)

[BHarm65]

So extra information.

I tried the DAX function SAMEPERIODLASTYEAR but this only will give me the previous year and not all the years before the selected year.

I need to calculate the value from 2018 unit the selected year.