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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

View solution in original post

19 REPLIES 19
amitchandak
Super User
Super User

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?

 

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

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?

 

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

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

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
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors