Forum Discussion
Create a table based on two tables
Hi Team
I have two tables that I need to combine based on a year criteria.
Table 1 shows the number or items sold during certain year, and the Table 2 is the database created after 2019 that contains the list of items sold sice 2019.
Table 1
| Year | Number |
| 2016 | 5 |
| 2017 | 8 |
| 2018 | 10 |
Table 2
| Date | Ref |
| 12/May/2019 | 10 |
| 13/Apr/2019 | 11 |
| 1/Oct/2019 | 10 |
| 8/Jun/2020 | 1 |
| 10/Jan/2021 | 07 |
| 10/Jun/2021 | 10 |
I need to create a table that will fetch the data in the column Number from the table 1 for the years 2016 to 2018 and for 2019 and forward the sum of the number of items sold during those years.
Result
| Year | # Articles sold |
| 2016 | 5 |
| 2017 | 8 |
| 2018 | 10 |
| 2019 | 3 |
| 2020 | 1 |
| 2021 | 2 |
Thanks a lot for your help
Hi,
This is my solution :
1- In Table 2 I create the Year column based on the date column in Power Query :
2- Append 2 tables and delete :
3- Create the below measure in new table :
# Article Sold = if(SELECTEDVALUE('Sheet220-1'[Number]) <> BLANK(),SELECTEDVALUE('Sheet220-1'[Number]),CALCULATE(COUNT('Sheet220-1'[Year])))Now could use the measure in Table visual or anywhere :Thanks for Kudos and please mark it as solution if it helps
1 Reply
Hi,
This is my solution :
1- In Table 2 I create the Year column based on the date column in Power Query :
2- Append 2 tables and delete :
3- Create the below measure in new table :
# Article Sold = if(SELECTEDVALUE('Sheet220-1'[Number]) <> BLANK(),SELECTEDVALUE('Sheet220-1'[Number]),CALCULATE(COUNT('Sheet220-1'[Year])))Now could use the measure in Table visual or anywhere :Thanks for Kudos and please mark it as solution if it helps