March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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
Solved! Go to 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
Check
https://www.dropbox.com/s/7tac5tu0b0bxvbn/day_taxes_problem.pbix?dl=0
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,
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?
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
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
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 |
After putting the correct tax table, are you able to get the desired result?
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
Change the first formula of Min date as
waoo! It works great, you're a crack. Thanks very much
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
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 |
@ybatistamayo does IDCost & Date makes unique record in Taxes table?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
YES, but keep in mind that the "Sales" table can have much more records. Thanks
@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
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
t is not the solution. records remain in the "sales" table without "taxes" since the dates in the last table contain unique dates
@ybatistamayo did you tested it? what made you think it is not the solution?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
yes, I did the test and this is the result, as you can see there are empty fields in the calculated columns
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
124 | |
87 | |
85 | |
70 | |
51 |
User | Count |
---|---|
205 | |
153 | |
97 | |
79 | |
69 |