Forum Discussion
DAX Measure/Column Help
see attached
Adjusted =
VAR yf =
SELECTEDVALUE ( 'Year-Factor'[Year] )
VAR a =
SUMMARIZE (
'Fact',
[Product],
[Year],
"sm",
VAR y = [Year]
RETURN
SUM ( 'Fact'[Price] )
* IF (
yf = "None",
1,
COALESCE (
CALCULATE (
PRODUCTX ( 'Year-Factor', [Factor] ),
'Year-Factor'[Year] <= yf,
'Year-Factor'[Year] > y
),
1
)
)
)
RETURN
SUMX ( a, [sm] )
See attached
Thanks lbendlin
just last help.
we have altred the Year-Factor table by adding an extra column "Factor2"
Year Factor Factor2
2021 1.035 0.98
2022 1.035 0.98
2023 1.035 0.98
None 1 1
now we need to consider Factor2 in the calculation when Year-Factor.Year < Fact.Year
for rest your given forumula is correct.
ex. for Product D - which belongs to year 2023
when they select 2021
expected output -
output= price * 2022 factor2 * 2023 factor2 (10*0.98*0.98)
when they select 2022
output= price * 2023 factor2 (10*0.98)
expected output. (you just need to add the scenario for the bold one in the current formula)
| filter selection in slicer | ||||||
| Product | Year | Price | 2021 selected | 2022 selected | 2023 selected | None |
| A | 2020 | 10 | 10.35 | 10.71225 | 11.08717875 | 10 |
| B | 2021 | 15 | 15 | 15.525 | 16.068375 | 15 |
| C | 2022 | 20 | 19.6 | 20 | 20.7 | 20 |
| D | 2023 | 10 | 9.604 | 9.8 | 10 | 10 |
You can change the PRODUCTX calculation accordingly.