Measure instead of calculated columns

Question

Hi,&nbsp;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.&nbsp;&nbsp;I want for each invoice row the difference between invoiced and recalculated amount using these tables.&nbsp;&nbsp;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.&nbsp;can i get this done without using calculated columns?&nbsp;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&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Client id&nbsp;Start dateEnd dateProduct IDStarting amount (units)Ending amount (units)discount&nbsp;Listprice&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;A1-1-202231-12-20231001000001,53&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;B1-1-202231-12-202315035000,52&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;B1-1-202231-12-20231535001000012&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;PricingTable&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DateProduct IDPricingmethod1Pricingmethod2Pricingmethod3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;10-5-2023152,833,2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;23-3-2023152,52,852,65&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;InvoiceTable&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DateinvoicenumerClient&nbsp;product idTotal amount unitsPriceTotal €Pricingmetod invoicedRecalculation methodPrice recalculatedRecalculated discountRecalculated total €Difference23-3-2023100A105002,5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1.250,00132,651,5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 575,00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -675,0010-5-2023102B1510002,8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2.800,001230,5&nbsp;&nbsp;&nbsp;&nbsp; 2.500,00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -300,0023-3-2023101B1536002,5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9.000,00122,851&nbsp;&nbsp;&nbsp;&nbsp; 6.660,00&nbsp;&nbsp; -2.340,0010-5-2023103A256002,8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1.680,001None-&nbsp;&nbsp;&nbsp;

amitchandak · Answer

JLMLV00 , You have force that calculation at row level in meausre 
&nbsp;
Example
Sumx(Table,
var _col = &lt;Column calc&gt;
return &lt;More calc&gt; )
&nbsp;
or
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM

jlmlv00 · Answer

Thanks a lot,&nbsp;hope you can help me out with this one,&nbsp;Client A has more than one rows in the recalculationtable&nbsp;from 1-1-23 till 30-6-23 their recalculated pricing method is 2&nbsp;from 1-7-23 till 31-12-23 pricing method is 3&nbsp;with Sumx &lt;=max(date) etc.&nbsp; 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.&nbsp;SUMX(Filter(Invoicetable,Invoicetable[date]&gt;=Max(Recalculationtable[startdate]&amp;&amp;Invoicetable[date]&lt;=Max(Recalculationtable[Enddate]), [Listprice])

Forum Discussion

Measure instead of calculated columns

2 Replies

Related content

DAX Calculated Measure

DAX Measure for Ranking the valid rows and calculating the average rating

Measure or Calculated Column

Measure to calculate

Measure/Model calculation support needed

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	-