Forum Discussion
DAX Measure/Column Help
- 3 years ago
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.
- arsene493 years agoHelper I
lbendlin tried playing around with that function, couldn't get the exact output. can you please help?
- lbendlin3 years agoSuper User
Have you replaced factor with factor2 in the formula?
it is not clear to me what your expected outcome is. Please clearly indicate the expected outcome for all scenarios.
- arsene493 years agoHelper I
Thanks for respoding lbendlin . let me explain the scenarios -
there are 3 scenarios based on the year filter selction we make and year in Fact where each product belongs.
1.Year Selected silcer (from Year-Factor) > Fact Table Year (for each product) = here we need to consider factor to multiply price
2.Year Selected (Year-Factor) < Fact Table Year = here we need to consider factor2 to multiply price
3.Year Selected (Year-Factor) = Fact Table Year and Year Selected (Year-Factor)="None" = here we need to consider 1 to multiply priceex. when we select 2021 in the filter
if we look at the fact table:
expected output -
for product A which belongs to year 2020 (Here Year Selected silcer (from Year-Factor) > Fact Table Year (for each product))
output= price * 2021factor = (10*1.04)
for product B which belongs to year 2021 (Here Year Selected (Year-Factor) = Fact Table Year)
output= price * 1 = (15*1)
for product C which belongs to year 2022 (Here Year Selected (Year-Factor) < Fact Table Year)
output= price * 2021factor2 = (20*0.98)
for product D which belongs to year 2023 (Here Year Selected (Year-Factor) < Fact Table Year)
output= price * 2022factor2 *2021factor2= (10*0.98*0.98)ex. when we select 2022 in the filter
if we look at the fact table:
expected output -
for product A which belongs to year 2020
output= price * 2021factor*2022factor = (10*1.04*1.04)
for product B which belongs to year 2021
output= price * 2021factor = (15*1.04)
for product C which belongs to year 2022
output= price * 1 = (20*1)
for product D which belongs to year 2023
output= price *2022factor2= (10*0.98)final measure output = sum of Product A output + Product B output + Product C output + Product D output
Year-Factor table-
Year Factor Factor2
2021 1.035 0.98
2022 1.035 0.98
2023 1.035 0.98
None 1 1expected output -
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