Forum Discussion
DAX Measure/Column Help
see attached
lbendlin Thanks for the reply. i have added a table at the end of the question which shows the expected output when each value of the Year-Factor slicer is selected. let me know if you need any further information.
lbendlin i have added expected outcome for all products and Year-Filter selections. did you get chance to look?
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 1now 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.