How to multiply single value from one coloum to another using dax

[Shahzz77]

I want to make a calcultion in DAX which includes Multiplication of single value with rest column values

For example in table

Ans = (Percentage 2020*sales2021) + (Percentage 2019*sales2021) +(Percentage 2018*sales2021)+(Percentage 2017*sales2021)

I want to create a measure for the above calculation which gives me the sale of 4 year. 

Year	Sales	Percentage	Sales of 4 year
2017	1000	0.2
2018	2000	0.2
2019	3000	0.2
2020	4000	0.2
2021	5000	0.2	ans

[v-henryk-mstf]

Hi @Shahzz77 ,

According to your description, I did the test with the following reference:

M = 
VAR sel_year =
    SELECTEDVALUE ( 'Table'[Year] )
RETURN
    CALCULATE ( MAX ( 'Table'[Sales] ), 'Table'[Year] = sel_year )
        * CALCULATE (
            MAX ( 'Table'[Percentage] ),
            'Table'[Year] <= sel_year - 1
                && 'Table'[Year] >= sel_year - 4
        )

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Shahzz77]

what if my percentage values are different then i cannot use max in my DAX. And also i have parameter for year sales like 4 3 2 to and the calculation changes accordingly . 4 year sales =2021*(percentage (2017+2018+2019+2020) and if 3 years = 2021*percentage(2018+2019+2020)

PERCENTAGE

0.2

0.4

0.3

0.5

0.2

[lbendlin]

Something like this (meta code)

ans = 
var y = selectedvalue(year)
return sumx(filter(table,year<y && year>y-6),percentage)*selectedvalue(sales)

[Shahzz77]

Will this work in power bi dax?

ans = ?

what syntax i have to give 

would be hlpful if u can state a example.

[lbendlin]

Have you tried to use the DAX formula I provided?  You'll need to adjust it according to your actual table and column names.

[Shahzz77]

Yes i tried but  it is showing error when i am selecting  columns in my table.

my actual table and colum names are

reserve =

Var y = selectedvalue(timeperiod)

return sumx (filter(table,year<y && year>y-6), percentage)* selectedvalue(sales)

It is showing error in filter table and also i am not able to select my percentage column.

[lbendlin]

You need to create a measure and in it need to specify the actual table names.

[Shahzz77]

sumx (filter(table,year<y && year>y-6), percentage)* selectedvalue(sales)

What is y-6 .. why it is used

[lbendlin]

You had indicated that you wanted to see the previous four years of data .