Forum Discussion
Need Help: Yearly step-down calculation based on a fixed percentage
Hi,
I'm new to DAX and I need help with coming up with a DAX computation
The scenario here is that at the start of each new year from [Period No.] = 8 onwards, I need to compute a degredation.
The first year (ie. [Period No. = 8]), there will be no degredation therefore 100% but in subsequent years, there will be a degredation of 0.7% p.a. and this degradation will only happen at the start of the year.
Please help on what DAX expression should be written to achieve the results above.
Thanks
| Year | Year. No | Period No. | Start date | End date | Degredation |
| 2018 | 1 | 1 | 1-Apr-18 | 30-Jun-18 | |
| 2018 | 1 | 2 | 1-Jul-18 | 30-Sep-18 | |
| 2018 | 1 | 3 | 1-Oct-18 | 31-Dec-18 | |
| 2019 | 2 | 4 | 1-Jan-19 | 31-Mar-19 | |
| 2019 | 2 | 5 | 1-Apr-19 | 30-Jun-19 | |
| 2019 | 2 | 6 | 1-Jul-19 | 30-Sep-19 | |
| 2019 | 2 | 7 | 1-Oct-19 | 31-Dec-19 | |
| 2020 | 3 | 8 | 1-Jan-20 | 31-Mar-20 | 100.0% |
| 2020 | 3 | 9 | 1-Apr-20 | 30-Jun-20 | 100.0% |
| 2020 | 3 | 10 | 1-Jul-20 | 30-Sep-20 | 100.0% |
| 2020 | 3 | 11 | 1-Oct-20 | 31-Dec-20 | 100.0% |
| 2021 | 4 | 12 | 1-Jan-21 | 31-Mar-21 | 99.3% |
| 2021 | 4 | 13 | 1-Apr-21 | 30-Jun-21 | 99.3% |
| 2021 | 4 | 14 | 1-Jul-21 | 30-Sep-21 | 99.3% |
| 2021 | 4 | 15 | 1-Oct-21 | 31-Dec-21 | 99.3% |
| 2022 | 5 | 16 | 1-Jan-22 | 31-Mar-22 | 98.6% |
| 2022 | 5 | 17 | 1-Apr-22 | 30-Jun-22 | 98.6% |
| 2022 | 5 | 18 | 1-Jul-22 | 30-Sep-22 | 98.6% |
| 2022 | 5 | 19 | 1-Oct-22 | 31-Dec-22 | 98.6% |
| 2023 | 6 | 20 | 1-Jan-23 | 31-Mar-23 | 97.9% |
| 2023 | 6 | 21 | 1-Apr-23 | 30-Jun-23 | 97.9% |
| 2023 | 6 | 22 | 1-Jul-23 | 30-Sep-23 | 97.9% |
| 2023 | 6 | 23 | 1-Oct-23 | 31-Dec-23 | 97.9% |
| 2024 | 7 | 24 | 1-Jan-24 | 31-Mar-24 | 97.2% |
Anonymous
You may use DAX below to add a calculated column.
Column = VAR p = 'Table'[Period No.] - 8 RETURN IF ( p >= 0, 1 - QUOTIENT ( p, 4 ) * 0.7 / 100 )
3 Replies
Probably something like:
Degradation = VAR __period = [Period] VAR __degradation = IF([Period] < 8, BLANK(), INT( (__period - 😎 / 4) * .7 ) RETURN 100 - __degradationAnonymous
You may use DAX below to add a calculated column.
Column = VAR p = 'Table'[Period No.] - 8 RETURN IF ( p >= 0, 1 - QUOTIENT ( p, 4 ) * 0.7 / 100 )
