Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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% |
Solved! Go to Solution.
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 )
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 )
Probably something like:
Degradation =
VAR __period = [Period]
VAR __degradation =
IF([Period] < 8,
BLANK(),
INT( (__period - 😎 / 4) * .7
)
RETURN 100 - __degradation
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |