SUBTRACTION BETWEEN DIFFERENT TABLES, WITH FILTER BY DATE

[beuthner71]

Dear,

I'm new to Power Bi (six months more or less), and so far I've solved all the questions by searching for tutorials and examples on the internet, but unfortunately I'm not finding a solution to my problem anywhere.

I have two spreadsheets (DAP & DERIVA) with more or less 30,000 lines each.

In the DAP & DERIVA spreadsheet I have the same columns:

(A) CODE: It would be the same as the product code. NO code in the DAP spreadsheet is the same as the one in the DERIVA spreadsheet.

(B) DATE: Date on which the FEE was collected for the sale of the product. Each CODE is checked several times a month to find out the FEE for the day.

(C) FEE: Fee negotiated on the day of verification.

What I need is:

That the FEES of all CODES grouped in the DAP spreadsheet, and filtered by dates, are subtracted from all CODES in the DERIVA spreadsheet (does not need to be grouped), but which have the same DATE in the DAP spreadsheet.

EXAMPLE:

ACTION 1: GROUPED DAP BY CODE

ACTION 2: IDENTIFY WHICH DATES ARE THE SAME IN THE DERIVA SPREADSHEET, REGARDLESS OF THE CODE.

ACTION 3: SUBTRACT FEES PER GROUPING.

ACTION 4: TRANSFORM THE RESULT OF SUBTRACTIONS IN ABSOLUTES

ACTION 5: LIST THE LOWEST VALUES PER GROUP.

If anyone in the group has a link of any kind (PDF, Youtube, DOC, etc.) that helps me understand how to deal with this problem, I would greatly appreciate it.

The explanations I found on the internet talk about multiplication, average, division, but I couldn't find anything about subtraction of values with dates which are repeated.

I spent some time trying to understand Line Context and Filter Context, as I believe that (among other things) this is a subject that I need to delve deeper into.

[v-tangjie-msft · Accepted solution]

Hi @beuthner71 ,

According to your description, here are my steps you can follow as a solution.

(1) Click on "transform data" to enter the power query, merge the two tables with the date column and expand it. Refer to the pbix file.

(2) We can create measures.

ABS_Fee = ABS(SUM('Merge1'[DAP_FEE])-SUM('Merge1'[DERIVA_FEE]))

Flag = IF([ABS_Fee]=MINX(FILTER(ALLSELECTED('Merge1'),'Merge1'[DAP_CODE]=MAX('Merge1'[DAP_CODE]) && 'Merge1'[DATE]=MAX('Merge1'[DATE])),[ABS_Fee]),1,0)

(3) Place [Flag=1] on the screening of the visual object and then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[amitchandak]

@beuthner71 , Based on what I got so far is merge two tables based on date in Power query 

Merge Tables (Power Query) : https://youtu.be/zNrmbagO0Oo

Or create a dax column in DERIVA  using DAP

new column = DERIVA [Fee] - Sumx(filter(DAP, DERIVA [Date]= DAP[Date]) , DAP[FEE])

refer 4 ways (related, relatedtable, lookupvalue, sumx/minx/maxx with filter) to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8