Sum Product row based on lookup value

[BobbyTheBuilder]

Hi,

I have 12 columns one next to the other representing salaries per month. I also have another different table with pay periods (26 of them), and a multiplier for each month.

What I want is to add a column in the Salary table that computes the year to date salary, based on the number of payrolls lapsed (1 to 26). Say I'm at payroll 13, for each row, the function would lookup 13 in the Multiplier Table, and multiply that row with the all of my Salary Table months, so that some months are multiplied by 1, some by a fraction, and the rest by 0.

Here is the Salary Table:

Person	January	February	March	April	May	June	July	August	September	October	November	December
Mary	100	100	100	100	100	100	100	100	100	100	100	100
David	90	90	90	90	90	90	90	90	90	90	90	90
Joseph	80	80	80	80	80	80	80	80	80	80	80	80

Here is the Multiplier table:

# Payrolls	2021-01-01	2021-02-01	2021-03-01	2021-04-01	2021-05-01	2021-06-01	2021-07-01	2021-08-01	2021-09-01	2021-10-01	2021-11-01	2021-12-01
1	0,4516129	0	0	0	0	0	0	0	0	0	0	0
2	0,9032258	0	0	0	0	0	0	0	0	0	0	0
3	1	0,3928571	0	0	0	0	0	0	0	0	0	0
4	1	0,8928571	0	0	0	0	0	0	0	0	0	0
5	1	1	0,3548387	0	0	0	0	0	0	0	0	0
6	1	1	0,8064516	0	0	0	0	0	0	0	0	0
7	1	1	1	0,2666667	0	0	0	0	0	0	0	0
8	1	1	1	0,7333333	0	0	0	0	0	0	0	0
9	1	1	1	1	0,1935484	0	0	0	0	0	0	0
10	1	1	1	1	0,6451613	0	0	0	0	0	0	0
11	1	1	1	1	1	0,1	0	0	0	0	0	0
12	1	1	1	1	1	0,5666667	0	0	0	0	0	0
13	1	1	1	1	1	1	0,0322581	0	0	0	0	0
14	1	1	1	1	1	1	0,483871	0	0	0	0	0
15	1	1	1	1	1	1	0,9354839	0	0	0	0	0
16	1	1	1	1	1	1	1	0,3870968	0	0	0	0
17	1	1	1	1	1	1	1	0,8387097	0	0	0	0
18	1	1	1	1	1	1	1	1	0,3	0	0	0
19	1	1	1	1	1	1	1	1	0,7666667	0	0	0
20	1	1	1	1	1	1	1	1	1	0,2258065	0	0
21	1	1	1	1	1	1	1	1	1	0,6774194	0	0
22	1	1	1	1	1	1	1	1	1	1	0,1333333	0
23	1	1	1	1	1	1	1	1	1	1	0,6	0
24	1	1	1	1	1	1	1	1	1	1	1	0,0645161
25	1	1	1	1	1	1	1	1	1	1	1	0,516129
26	1	1	1	1	1	1	1	1	1	1	1	0,9677419

Thanks!

[Anonymous]

This can be done in DAX, but we will have to write a little bit of unnecessary DAX code to achieve this. But from the "best practices" point of view, in any data model or table, ideally you should have only one column/field for one particular type of data. For example, your salary table should look like this...

Person	Month	Salary
Mary	January	100
Mary	February	100
Mary	March	100
…	…	…
…	…	…
…	…	…
Joseph	October	80
Joseph	November	80
Joseph	December	80

Similarly, your Payroll table should have the following structure...

# Payrolls	Month	Multiplier
1	1-Jan-21	0.4516129
2	1-Jan-21	0.9032258
3	1-Jan-21	1
4	1-Jan-21	1
5	1-Jan-21	1
…	…	…
…	…	…
…	…	…
24	1-Dec-21	0.0645161
25	1-Dec-21	0.516129
26	1-Dec-21	0.9677419

Month, Date, etc... should have only one field instead of having a column for each month or each date.

Before, giving you the solution, I would like to know the following...

1) What is your data source? Is it an excel file? Because it unlikely that any database will store the information in this fashion.

2) Is it possible for you to convert the data into the form I suggested at the source itself?

3) If that is not possible, are you okay to pivot them in PowerQuery while importing the data into Power BI Desktop?

4) Is there any specific reason for you to have the data in the way you have kept it other than the reason that you want to see or present it in that form in visuals? If visualization is the reason, it can be handled in visuals itself. 

5) You said that if you are in Payroll 13, the calculation should happen accordingly? But how does the system know that you are in Payroll 13? Is it based on the current date? or is it based on a slicer or filter where the user will choose the current payroll number? While writing the DAX code, we need this information also to calculate the result.

I would like to tell you that, writing DAX code is one skill, but having the data model in the best possible way following the best practices is another important thing. Because the way data is stored will make your DAX codes easier to write. Although with DAX skills, you can do nearly all kinds of things you can imagine, you should try to model your data correctly, and then the results can be achieved with simpler DAX codes.