Forum Discussion
Filtering the tables based on date and merging for comparison
I have two tables with the same fields. Just the difference between those two tables is the required delivery date.
Both the tables have different excel sheet as their source as I am extracting data from SAP every week.
Table 1 : Current week table. Filters applied on Required delivery date.
I have a slicer for current week Required delivery date which allows me to select my time frame.
| Material | Material description | Count of sales order | Confirmed quantity | Price current week |
| 001 | Extension cable | 83 | 32 | $2000 |
| 1236 | Monitor | 64 | 21 | $10000 |
| 47381 | Electrode | 76 | 54 | $4000 |
| 73632 | Temperature Probe | 32 | 32 | $30022 |
| 36341 | Pads | 6 | 80 | $2233 |
Table 2 : Previous week table. Filters applied on Required delivery date.
I have a slicer for previous week Required delivery date which allows me to select my time frame.
| Material | Material description | Count of sales order | Confirmed quantity | Price previous week |
| 001 | Extension cable | 23 | 31 | $1200 |
| 1236 | Monitor | 32 | 54 | $50000 |
| 47381 | Electrode | 43 | 65 | $5500 |
I have created above two tables in powerBI using Table visual. Now I want to merge these two tables based on unique id - Material.
Resultant table that I would like to see -
| Material | Material description | Count of sales order | Confirmed quantity | Price current week | Price previous week |
| 001 | Extension cable | 83 | 32 | $2000 | $1200 |
| 1236 | Monitor | 64 | 21 | $10000 | $50000 |
| 47381 | Electrode | 76 | 54 | $4000 | $5500 |
| 73632 | Temperature Probe | 32 | 32 | $30022 | null |
| 36341 | Pads | 6 | 80 | $2233 | null |
I tried left joint query but it does not work on filtered table. I would like to merge these two tables after applying my desired filter dates on each table.
The reason is, I would like to see the difference in price between current and previous week. I will create an extra column "Delta" to see the difference and hence I need this view.
Can anyone please help. I am bit new to powerBI.
Thanks.
6 Replies
You can achieve the result you want by using the DAX formula in Power BI.
You can create a calculated table as follows:
1. Create a new calculated table using the DAX formula below:
Result = UNION(Table1, Table2)2. Create a calculated column to show the delta value between the two prices. The formula should be:
Delta = [Price current week] - [Price previous week]3. In order to have a single row for each material, you can use the DAX formula:
Result = SUMMARIZE(Result, Result[Material], "Count of sales order", SUM(Result[Count of sales order]), "Confirmed quantity", SUM(Result[Confirmed quantity]), "Price current week", SUM(Result[Price current week]), "Price previous week", SUM(Result[Price previous week]), "Delta", SUM(Result[Delta]))This should give you a new table that contains a single row for each material, with the summarized values of the count of sales order, confirmed quantity, prices and delta.
Let me know if you might need further assistance.
Thank you for reverting on the question. It does not solve the problem. Let me try to explain more if it helps.
Excel 1 (current week data) - This has aprox 20 columns in source file
Excel 2 (Previous week data) - This also has aprox 20 columns as above in the source file.
I have used these sources to create table visuals by selecting specific columns of my choice and applying specific filters.
Table 1 - current week data is displayed and it includes calculated columns. Date filter of 1 Jan 2023 to 8 Jan 2023 is applied . Source is excel 1.
Table 2 - previous week data is displayed and it includes calculated columns. Date filter of 8 Jan 2023 to 15 Jan 2023 is applied . Source is excel 2.
I used to do this in excel and now I am doing it in powerBI to reduce my time for weekly reports.
In terms of excel, I would vlookup the material number from table 1 in table 2 and return the price value from table 2. This will give me current and previous week price columns side by side and then I can calculate the delta.
In powerBI - how can I merge the tables after I have applied date filter using slicer?
