Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be 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

Reply
ybatistamayo
Helper III
Helper III

RelationShips an calculate column

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

1 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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

View solution in original post

19 REPLIES 19
amitchandak
Super User
Super User

Check

 

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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,

@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?

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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

Screenshot 2019-12-03 04.58.18.png

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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

PRODUCTIDCOSTDATEQUANTITYUNIT PRICETAXES.AMOUNT(COLUMN)TAXES. PERCENTAGE(COLUMN)FINAL UNIT PRICE 
A10101-01-18121.50.23.7
B10205-01-18142.50.26.7
C10310-01-18163.50.910.4
D10425-01-18184.51.614.1
A10101-02-18121.50.23.7
B10205-02-18142.50.26.7
C10310-03-18163.50.910.4
D10425-04-18184.51.614.1
A10101-04-18121.50.23.7
B10205-05-18142.50.26.7
C10310-06-18163.50.910.4
D10425-08-18184.51.614.1
C10310-09-18163.50.910.4
D10425-10-18184.51.614.1
A10101-11-18121.50.23.7
A10101-02-19123.50.225.72
B10205-02-19144.50.248.74
C10310-03-19165.50.9612.46
D10425-03-19186.51.6816.18
A10101-03-19123.50.225.72
B10205-04-19144.50.248.74
C10310-05-19165.50.9612.46
D10425-05-19186.51.6816.18
A10101-06-19123.50.225.72
B10205-07-19144.50.248.74
C10310-08-19165.50.9612.46
D10425-09-19186.51.6816.18
D10401-10-19186.51.6816.18
D10425-10-19186.51.6816.18
A10102-10-19123.51116.5
B10215-10-19144.550.288.83
C10316-10-19165.551.0212.57
D10417-10-19186.551.7616.31
A10101-11-19123.550.245.79
B10205-11-19144.550.288.83
C10301-12-19165.551.0212.57

 

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

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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

Min date = MAxX(FILTER(TAXES,TAXES[IDCOST]=SALES[IDCOST]&& TAXES[ID]=SALES[PRODUCT] && TAXES[DATE] <= SALES[DATE]),TAXES[DATE])
Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

this is de correct taxes table

IDIDCOSTDATEAMOUNTPERCENTAGE
A10101-01-181.510
B10201-01-182.55
C10301-01-183.515
D10401-01-004.520
A10101-01-193.511
B10201-01-194.56
C10301-01-195.516
D10401-01-196.521
A10112-10-194.5512
B10212-10-193.557
C10312-10-194.5517
D10412-10-195.5522
parry2k
Super User
Super User

@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

Sin título.png

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.