Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I want to calculate de difference between invoiced amount (€) and a recalculated amount. I get it done with calculated columns in my FactInvoices table but instead i would rather use a measure if this is possible.
I want for each invoice row the difference between invoiced and recalculated amount using these tables.
Some of the clients get at the end of a period a recalculated price, this price is based on another pricelist in pricingsmethod table with conditions such as product ID, between start and end date and invoiced units.
can i get this done without using calculated columns?
In table invoices is an example of the invoices the columheaders with red text colour is expected output (and not a part of original table)
RecalculationTable | ||||||||||||
Client id | Start date | End date | Product ID | Starting amount (units) | Ending amount (units) | discount | Listprice | |||||
A | 1-1-2022 | 31-12-2023 | 10 | 0 | 100000 | 1,5 | 3 | |||||
B | 1-1-2022 | 31-12-2023 | 15 | 0 | 3500 | 0,5 | 2 | |||||
B | 1-1-2022 | 31-12-2023 | 15 | 3500 | 10000 | 1 | 2 | |||||
PricingTable | ||||||||||||
Date | Product ID | Pricingmethod1 | Pricingmethod2 | Pricingmethod3 | ||||||||
10-5-2023 | 15 | 2,8 | 3 | 3,2 | ||||||||
23-3-2023 | 15 | 2,5 | 2,85 | 2,65 | ||||||||
InvoiceTable | ||||||||||||
Date | invoicenumer | Client | product id | Total amount units | Price | Total € | Pricingmetod invoiced | Recalculation method | Price recalculated | Recalculated discount | Recalculated total € | Difference |
23-3-2023 | 100 | A | 10 | 500 | 2,5 | 1.250,00 | 1 | 3 | 2,65 | 1,5 | 575,00 | -675,00 |
10-5-2023 | 102 | B | 15 | 1000 | 2,8 | 2.800,00 | 1 | 2 | 3 | 0,5 | 2.500,00 | -300,00 |
23-3-2023 | 101 | B | 15 | 3600 | 2,5 | 9.000,00 | 1 | 2 | 2,85 | 1 | 6.660,00 | -2.340,00 |
10-5-2023 | 103 | A | 25 | 600 | 2,8 | 1.680,00 | 1 | None | - |
@JLMLV00 , You have force that calculation at row level in meausre
Example
Sumx(Table,
var _col = <Column calc>
return <More calc> )
or
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM
Thanks a lot,
hope you can help me out with this one,
Client A has more than one rows in the recalculationtable
from 1-1-23 till 30-6-23 their recalculated pricing method is 2
from 1-7-23 till 31-12-23 pricing method is 3
with Sumx <=max(date) etc. the invoicerows from 1-1-23 till 30-6-23 wont show their recalculation pricing, because he's gonna use the max dates of the clients, when used MIN() the invoice rows after 30-6-23 wont show up.
SUMX(Filter(Invoicetable,Invoicetable[date]>=Max(Recalculationtable[startdate]&&Invoicetable[date]<=Max(Recalculationtable[Enddate]), [Listprice])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |