Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Varshi288
Resolver I
Resolver I

Avg of last 2 years

 

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.

 

Varshi288_0-1709277960540.png

 

PeriodYearColumnSort
2017A31/12/2017-21
2018A31/12/2018-12
2019A31/12/201903
2020A31/12/2020124
2021A31/12/2021245
2022A31/12/2022366
2023A31/12/2023487
2024E31/12/2024608
2024RF_prev31/12/2024  
2023RF_prev (RF2309)31/12/2023  
2024Estimate31/12/2024121
2025E31/12/2025242
2026E31/12/2026363
2027E31/12/2027484
2028E31/12/2028605
2 ACCEPTED SOLUTIONS
v-heq-msft
Community Support
Community Support

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:

vheqmsft_0-1710314379236.png

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

vheqmsft_1-1710314496755.png

Final output

vheqmsft_2-1710314518572.png

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

 

 

 

View solution in original post

Thanks Albert it worked after some tweaks in date table.

View solution in original post

4 REPLIES 4
v-heq-msft
Community Support
Community Support

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:

vheqmsft_0-1710314379236.png

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

vheqmsft_1-1710314496755.png

Final output

vheqmsft_2-1710314518572.png

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

 

 

 

Thanks Albert it worked after some tweaks in date table.

amitchandak
Super User
Super User

@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])))

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])))))



Varshi288_0-1709534126943.png

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.