Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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
Thanks Albert it worked after some tweaks in date table.
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
Thanks Albert it worked after some tweaks in date table.
@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.
User | Count |
---|---|
97 | |
87 | |
77 | |
67 | |
63 |
User | Count |
---|---|
112 | |
96 | |
95 | |
67 | |
65 |