Forum Discussion
DAX Measure/Column Help
Hi All, Need a help to create DAX measure/column.
There are 2 tables, below is the sample data
1.Year-Factor
which has Factor for each year
Year Factor
2021 1.035
2022 1.035
2023 1.035
None 1
2.Fact
there will be muliple products, multiple years and price
Product Year Price
A 2020 10
B 2021 15
C 2022 20
Requirement -
there will be Year slicer in the page from Year-Factor table and user will be choosing the year
ex. when they select 2022
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 * 2022factor
final measure output - sum of Product A output + Product B output + Product C output
ex. when they select 2023
expected output -
for product A which belongs to year 2020
output= price * 2021factor * 2022factor * 2023factor(10*1.04*1.04*1.04)
for product B which belongs to year 2021
output= price * 2022factor *2023factor
final measure output - sum of Product A output + Product B output + Product C output
ex. when they select None,
for Product A , measure output is just Price
expected output when each year selected:
| filter selection in slicer | ||||||
| Product | Year | Price | 2021 selected | 2022 selected | 2023 selected | None Selected |
| A | 2020 | 10 | 10.35 | 10.71225 | 11.08717875 | 10 |
| B | 2021 | 15 | 15 | 15.525 | 16.068375 | 15 |
| C | 2022 | 20 | 20 | 20 | 20.7 | 20 |
see attached
12 Replies
there will be Year slicer in the page from Year-Factor table and user will be choosing the yearFor that to work the Year-Factor table must be disconnected.
Your expected output is unclear for products that fall outside the limits. Please indicate the expected outcome for all products and Year-Filter selections.
TotalCost=
VAR selected=SELECTEDVALUE(Year-Factor[Year])
VAR summed = SUMMARIZED('Fact',[Product],[Year],"PY")
VAR year='Year-Factor'[Year]
RETURN
'Fact'[Price]*(year+1,[Factor],"yf")*(yf+1,[Factor])
RETURN
sumx(year,PY)
