Filtering the tables based on date and merging for comparison

[bansikpopat]

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. 

[Sahir_Maharaj]

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.

[bansikpopat]

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? 

[Sahir_Maharaj]

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]))

[Sahir_Maharaj]

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]

[Sahir_Maharaj]

1. Create a new calculated table using the DAX formula below:

Result = UNION(Table1, Table2)

[Sahir_Maharaj]

You can achieve the result you want by using the DAX formula in Power BI.

You can create a calculated table as follows: