Forum Discussion
Change different multiplier when different date
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
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/
4 Replies
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/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
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/
