RelationShips an calculate column

[ybatistamayo]

I HAVE TWO TABLES ONE FOR SALE AND OTHER TAXES.
I NEED TO CALCULATE THE
"FINAL UNIT PRICE" = "UNIT PRICE" + "TAXES.AMOUNT (COLUMN)" + "TAXES. PERCENTAGE (COLUMN)"
THE CHALLENGE IS THAT WITH DATE 12/10/2019 THE TAXES OF THE "TAXES" CHANGE CHANGED, THEREFORE, THE CALCULATIONS FROM THE 12/10/2019 DAY SHOULD BE ACHIEVED, TAKING INTO ACCOUNT THE NEW VALUES
I ATTACH A TABLE IN EXCEL FOR BETTER UNDERSTANDING WHAT I HOPE TO OBTAIN.

IT'S KNOWN THAT
AMOUNT (COLUMN) = TAXES [AMOUNT]
PERCENTAGE (COLUMN) = TAXES [PERCENTAGE] / 100 * SALES [UNIT PRICE]

PBIX: https://we.tl/t-aFHsGLJUE2

Excel: https://we.tl/t-Henr34LGhL

[amitchandak]

Check

https://www.dropbox.com/s/7tac5tu0b0bxvbn/day_taxes_problem.pbix?dl=0

[ybatistamayo]

HI, SOME VALUES DO NOT MATCH THE EXPECTED RESULT, FOR EXAMPLE RECORD # 2 PRODUCT B THE RESULTS OF AMOUNT TAXES MUST BE 2.5 AND IS GIVING 4.5. SEE ALSO RECORD # 6,

[amitchandak]

@ybatistamayo 

This how I have taken. You need to suggest changes

1. Matched Product and ID cost and taken a date below the date of the sale.  -- Min date

2. When I did not get the date in step 1, I removed the date of the sales and taken Min date.  -- Min date 2  .. I think might require a change. Do we need to remove ID cost in such cases?

[ybatistamayo]

LOGIC IS PARTIALLY CORRECT BUT FOR SOME REASON SOME RESULTS ARE CORRECT AND OTHERS ARE NOT.
HOW IT SHOULD WORK IS THIS:
1- THE TAXES CAN CHANGE IN DETERMINING DATE OF THE YEAR, SO THE VALUES THAT WERE COMPUTERED UNTIL THAT DATE MUST CONTINUE REFLECTING THE SAME AMOUNTS. THIS TO MAINTAIN CORRECT ACCOUNTING
- IN OUR CASE THE TAXES DID NOT CHANGE THROUGHOUT 2018, SO THE CALCULATIONS SHOULD BE COMPUTER TO THE 2018 YEAR.
- FROM JANUARY 1, 2019, TAXES CHANGED, SO THE SALES OF 2019 SHOULD BE ACCOUNTED BY THE TAXES OF JANUARY 1, 2019.
- ON OCTOBER 12, 2019, THE TAXES CHANGED AGAIN, SO FROM THAT DATE THE SALES MUST BEGIN TO REFLECT THE TAXES FROM THAT DATE AND MAINTAIN THOSE THAT WERE ACCOUNTED BEFORE THAT DATE

[amitchandak]

Take the example of row 2. Product B, ID cost 102.

Now the taxes only gives the two records. How to get that 2.5 % record

[ybatistamayo]

and this is de correct result. Sorry product B had the ID wrong. I have already rectified it
I have verified the results and I realize that all 2018 values are correct. for some reason the logic does not work for the year 2019

PRODUCT	IDCOST	DATE	QUANTITY	UNIT PRICE	TAXES.AMOUNT(COLUMN)	TAXES. PERCENTAGE(COLUMN)	FINAL UNIT PRICE
A	101	01-01-18	1	2	1.5	0.2	3.7
B	102	05-01-18	1	4	2.5	0.2	6.7
C	103	10-01-18	1	6	3.5	0.9	10.4
D	104	25-01-18	1	8	4.5	1.6	14.1
A	101	01-02-18	1	2	1.5	0.2	3.7
B	102	05-02-18	1	4	2.5	0.2	6.7
C	103	10-03-18	1	6	3.5	0.9	10.4
D	104	25-04-18	1	8	4.5	1.6	14.1
A	101	01-04-18	1	2	1.5	0.2	3.7
B	102	05-05-18	1	4	2.5	0.2	6.7
C	103	10-06-18	1	6	3.5	0.9	10.4
D	104	25-08-18	1	8	4.5	1.6	14.1
C	103	10-09-18	1	6	3.5	0.9	10.4
D	104	25-10-18	1	8	4.5	1.6	14.1
A	101	01-11-18	1	2	1.5	0.2	3.7
A	101	01-02-19	1	2	3.5	0.22	5.72
B	102	05-02-19	1	4	4.5	0.24	8.74
C	103	10-03-19	1	6	5.5	0.96	12.46
D	104	25-03-19	1	8	6.5	1.68	16.18
A	101	01-03-19	1	2	3.5	0.22	5.72
B	102	05-04-19	1	4	4.5	0.24	8.74
C	103	10-05-19	1	6	5.5	0.96	12.46
D	104	25-05-19	1	8	6.5	1.68	16.18
A	101	01-06-19	1	2	3.5	0.22	5.72
B	102	05-07-19	1	4	4.5	0.24	8.74
C	103	10-08-19	1	6	5.5	0.96	12.46
D	104	25-09-19	1	8	6.5	1.68	16.18
D	104	01-10-19	1	8	6.5	1.68	16.18
D	104	25-10-19	1	8	6.5	1.68	16.18
A	101	02-10-19	1	2	3.5	11	16.5
B	102	15-10-19	1	4	4.55	0.28	8.83
C	103	16-10-19	1	6	5.55	1.02	12.57
D	104	17-10-19	1	8	6.55	1.76	16.31
A	101	01-11-19	1	2	3.55	0.24	5.79
B	102	05-11-19	1	4	4.55	0.28	8.83
C	103	01-12-19	1	6	5.55	1.02	12.57

[amitchandak]

After putting the correct tax table, are you able to get the desired result?

[ybatistamayo]

I have verified the results and I realize that all 2018 values are correct. for some reason the logic does not work for the year 2019

[amitchandak]

Change the first formula of Min date as

Min date = MAxX(FILTER(TAXES,TAXES[IDCOST]=SALES[IDCOST]&& TAXES[ID]=SALES[PRODUCT] && TAXES[DATE] <= SALES[DATE]),TAXES[DATE])

[ybatistamayo]

waoo! It works great, you're a crack. Thanks very much

[amitchandak · Accepted solution]

Thanks for confirmation..

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[ybatistamayo]

this is de correct taxes table

ID	IDCOST	DATE	AMOUNT	PERCENTAGE
A	101	01-01-18	1.5	10
B	102	01-01-18	2.5	5
C	103	01-01-18	3.5	15
D	104	01-01-00	4.5	20
A	101	01-01-19	3.5	11
B	102	01-01-19	4.5	6
C	103	01-01-19	5.5	16
D	104	01-01-19	6.5	21
A	101	12-10-19	4.55	12
B	102	12-10-19	3.55	7
C	103	12-10-19	4.55	17
D	104	12-10-19	5.55	22

[parry2k]

@ybatistamayo does IDCost & Date makes unique record in Taxes table?

[ybatistamayo]

YES, but keep in mind that the "Sales" table can have much more records. Thanks

[parry2k]

@ybatistamayo so add a Key column in both the tables and set relationship on this new key column and relationship will many to one, many on sales side and one on taxes table side.

Key = FORMAT( TAXES[IDCOST], "General Number" ) & FORMAT ( TAXES[DATE], "YYYYMMDD" ) 


Key = FORMAT( SALES[IDCOST], "General Number" ) & FORMAT ( SALES[DATE], "YYYYMMDD" ) 

Once this relationship is created, you can RELATED function to get column value from Taxes table

[ybatistamayo]

t is not the solution. records remain in the "sales" table without "taxes" since the dates in the last table contain unique dates

[parry2k]

@ybatistamayo did you tested it? what made you think it is not the solution?

[ybatistamayo]

yes, I did the test and this is the result, as you can see there are empty fields in the calculated columns

[ybatistamayo]

PBIX: https://we.tl/t-BwQlmkQwi3