Change different multiplier when different date

[primolee]

Hello everyone,

I have advertisement spending data of each media Type in the following table.

Media	Date	Spending
Outdoor	2018/9/1	100000
Cable TV	2020/1/1	20000
Newspaper	2017/7/1	30000
Terr	2019/8/1	60000
Radio	2018/8/1	70000
Magazine	2018/6/1	50000
Outdoor	2016/12/1	40000
Cable	2020/6/1	120000
Newspaper	2020/12/1	80000
Terr.	2021/6/1	160000
Radio	2020/5/1	20000
Magazine	2019/3/1	40000

Because each kind of media has different weight at different period, here is my other table with weight information

Media Name	Start Date	End Date	Weight
Outdoor	2018/9/1	2021/6/30	0.8
Cable	2018/9/1	2021/6/30	0.068
Newspaper	2018/9/1	2021/6/30	0.326
Terr.	2018/9/1	2021/6/30	0.055
Radio	2018/9/1	2021/6/30	0.217
Magazine	2018/9/1	2021/6/30	0.631
Outdoor	2016/1/1	2018/8/31	0.7
Cable	2016/1/1	2018/8/31	0.06
Newspaper	2016/1/1	2018/8/31	0.4
Terr.	2016/1/1	2018/8/31	0.08
Radio	2016/1/1	2018/8/31	0.3
Magazine	2016/1/1	2018/8/31	0.65

What I want to do is to calculcate the weighted spending using the correct weight depending the date column.

For example, media and date of first row of spending data is outdoor 2018/9/1, and weight of outdoor between 2018/9/1 and 2021/6/30 is 0.8, therefore the weighted spending is 80000.

Whereas for the third row newspaper, because the date 2017/7/1 is between 2016/1/1 and 2018/8/31, therefore the weight is 0.4.

Following is what I hope to accomplish in a BI table visual.

Media	Date	Spending	Weight	Weighted Spending
Outdoor	2018/9/1	100000	0.8	80000
Cable	2020/1/1	20000	0.068	1360
Newspaper	2017/7/1	30000	0.4	12000

I want to add the "Weight" column to the first table.  Could someone please help me on doing this?

Thank you very much for your help.

Best regards,

David

[Jihwan_Kim · Accepted solution]

Hi, @primolee 

Please check the below picture and the sample pbix file's link dow below.

I created both measures and columns.

All measures and created columns are in the sample pbix file.

https://www.dropbox.com/s/5bhb72jux254dxl/primolee2.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/

[primolee]

Hello @Jihwan_Kim ,

Thank you again for your help.  Greatly appreciated!

To further extend the question.  Because we have different weight values for different people, is it possible to have a filter visual in the report, choose a person's weight setting, then calculate the weighted spending?

In this case, I have 1 more column as Name, just as John and Peter, and each of them have their weight setting.  Then make a name filter in the report.

Thank you very much.

Best regards,

David

[Jihwan_Kim]

Hi, @primolee 

Thank you for your feedback.

I am not sure how to create the person's name column, but I just simply created it to show as a table visualization.

Please check the below picture and the link down below, whether it is what you are looking for.

https://www.dropbox.com/s/5bhb72jux254dxl/primolee2.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/

[primolee]

Hello @Jihwan_Kim ,

Thank you so much for your prompt reply.

This is what I meant, add a "Name" column to the weight table.  Then use filter visual to single select name.

Column will not work, but measure seems to work fine.

https://drive.google.com/file/d/1BcFHRkQFESSs-T_hcYMl0XmBndZp5vVN/view?usp=sharing 

Thank you once again for your help!

Best regards,

David