Avg of last 2 years

[Varshi288]

Hi  All, 

I need to find the Avg of last 2 years for total capital employed for selected year. I found some solutions but it is different with my model. I have a Date tavle with 1day i.e last day of the year. Could any one help me. Thanks.

Period	Year	Column	Sort
2017A	31/12/2017	-2	1
2018A	31/12/2018	-1	2
2019A	31/12/2019	0	3
2020A	31/12/2020	12	4
2021A	31/12/2021	24	5
2022A	31/12/2022	36	6
2023A	31/12/2023	48	7
2024E	31/12/2024	60	8
2024RF_prev	31/12/2024
2023RF_prev (RF2309)	31/12/2023
2024Estimate	31/12/2024	12	1
2025E	31/12/2025	24	2
2026E	31/12/2026	36	3
2027E	31/12/2027	48	4
2028E	31/12/2028	60	5

[v-heq-msft · Accepted solution]

Hi @Varshi288 ,
Here some steps that I want to share, you can check them if they suitable for your requirement.
Here is my test data:

Create a calculated table accroding to Year column

Year = VALUES('Table'[Year].[Year])

Create a measure

AVERAGE PAST 2 YEARS = 
VAR _COUNT =
CALCULATE(
    DISTINCTCOUNT('Table'[Year]),
    FILTER(
        'Table',
        'Table'[Year].[Year] < SELECTEDVALUE('Year'[Year]) && 'Table'[Year].[Year] >= SELECTEDVALUE('Year'[Year]) - 2
    )
)
RETURN
CALCULATE(
    SUM('Table'[Column])/_COUNT,
    FILTER(
        'Table', 
        'Table'[Year].[Year] < SELECTEDVALUE('Year'[Year]) && 'Table'[Year].[Year] >= SELECTEDVALUE('Year'[Year]) - 2
    )
)

Use the new table column as the filed of slicer

Final output

Best regards,

Albert He

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

[v-heq-msft · Accepted solution]

Hi @Varshi288 ,
Here some steps that I want to share, you can check them if they suitable for your requirement.
Here is my test data:

Create a calculated table accroding to Year column

Year = VALUES('Table'[Year].[Year])

Create a measure

AVERAGE PAST 2 YEARS = 
VAR _COUNT =
CALCULATE(
    DISTINCTCOUNT('Table'[Year]),
    FILTER(
        'Table',
        'Table'[Year].[Year] < SELECTEDVALUE('Year'[Year]) && 'Table'[Year].[Year] >= SELECTEDVALUE('Year'[Year]) - 2
    )
)
RETURN
CALCULATE(
    SUM('Table'[Column])/_COUNT,
    FILTER(
        'Table', 
        'Table'[Year].[Year] < SELECTEDVALUE('Year'[Year]) && 'Table'[Year].[Year] >= SELECTEDVALUE('Year'[Year]) - 2
    )
)

Use the new table column as the filed of slicer

Final output

Best regards,

Albert He

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

[Varshi288 · Accepted solution]

Thanks Albert it worked after some tweaks in date table.

[amitchandak]

@Varshi288 , it is better to have a separate year/date table with year column , joined with the year/date of your table

Avg of last 2 year

CALCULATE(Averagex(Values('Date'[Year]), calculate(sum('Table'[Qty]))) ,filter(ALL('Date'),'Date'[Year]>=max('Date'[Year])-1 && 'Date'[Year]<=max('Date'[Year])))

[Varshi288]

Hi Amith, First of all thanks for your support.

I added a new saparete year column in the date table as shown in the snapshot. but this give me blank values.

CALCULATE(AVERAGEX(VALUES(Period[Date]),
CALCULATE(SUM(Data[Total Capital Employed]),FILTER(ALL(Period),Period[Year]>=MAX(Period[Year])-1 && Period[Year]<= MAX(Period[Year])))))