Forum Discussion

BHarm65's avatar
BHarm65
Frequent Visitor
2 years ago
Solved

Calculate sum over previous years

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
 
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

3 Replies

  • BHarm65's avatar
    BHarm65
    Frequent Visitor
    2 years ago

    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.

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    2 years ago

    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
)

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.