Creating Margin for mulitple customers

Question

Hi there,I have created a logic for one of the customer “A” with the below formula and it is working fine.PickUpCost-Cost =&nbsp;&nbsp;&nbsp; Var Cost = IF(Shipments[BilledOn]&lt; DATE(2019,04,01), (1-0.228),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; IF(Shipments[BilledOn]&gt; DATE(2019,04,01) ||Shipments[BilledOn]&lt; DATE(2020,04,01), (1-0.221), (0.219)))&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Return&nbsp;&nbsp;&nbsp; IF(LOOKUPVALUE('Financials'[Income],'Financials'[Invoice],Shipments[Invoice])-LOOKUPVALUE('Financials'[Cost],'Financials'[Invoice],Shipments[Invoice]) =&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LOOKUPVALUE('Financials'[Profit],'Financials'[Invoice],Shipments[Invoice]),&nbsp;&nbsp; IF(OR(LOOKUPVALUE('Financials'[ss],'Financials'[Invoice],Shipments[Invoice])&lt;=0.2,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; LOOKUPVALUE('Financials'[ss],'Financials'[Invoice],Shipments[Invoice])&gt;=0.3),&nbsp;&nbsp;&nbsp; Cost*LOOKUPVALUE('Financials'[PC- Income],'Financials'[Invoice],Shipments[Invoice]),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LOOKUPVALUE('Financials'[PC- cost],&nbsp; 'Financials'[Invoice],Shipments[Invoice])),&nbsp;&nbsp;&nbsp; Cost*LOOKUPVALUE('Financials'[PC- Income],'Financials'[Invoice],Shipments[Invoice]))&nbsp;Now I am trying to create same formula for multiple customers with different Margins but it is not workingIndexStart DateEnd Date&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; C&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; D11/1/20183/31/20190.2280.2090.2280.22824/1/20193/31/20200.2210.2090.2210.22134/1/20203/31/20210.2190.2090.2190.219&nbsp;Could anyone please help how to create formula for all customers, its very urgent and very important.I have tried using(CALCULATE (FIRSTNONBLANK('Financials'[Income],1),FILTER(ALL('Financials'), 'Scope'[Invoice] = 'Shipments'[Invoice]))instead of LOOKUP Function but still it is not workingPlease let me know if any other details are requiredThanks in advance.

bheepatel · Answer

Hi&nbsp;Anonymous&nbsp;&nbsp;One possible solution is to pivot your table so that you have your table in the same format as Table B below.&nbsp;You can use the pivot options available to you in Power Query.&nbsp;&nbsp;Once you have that, you can create the measure similar to the one below, where you can change the Customer and BilledOnDate variables to suit your needs. Below screenshots show you examples for two different customers and billing dates.&nbsp;&nbsp;Hope this helps!

anonymous · Answer

Hi there,I have created a logic for one of the customer “A” with the below formula and it is working fine.PickUpCost-Cost =&nbsp;&nbsp;&nbsp; Var Cost = IF(Shipments[BilledOn]&lt; DATE(2019,04,01), (1-0.228),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; IF(Shipments[BilledOn]&gt; DATE(2019,04,01) ||Shipments[BilledOn]&lt; DATE(2020,04,01), (1-0.221), (0.219)))&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Return&nbsp;&nbsp;&nbsp; IF(LOOKUPVALUE('Financials'[Income],'Financials'[Invoice],Shipments[Invoice])-LOOKUPVALUE('Financials'[Cost],'Financials'[Invoice],Shipments[Invoice]) =&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LOOKUPVALUE('Financials'[Profit],'Financials'[Invoice],Shipments[Invoice]),&nbsp;&nbsp; IF(OR(LOOKUPVALUE('Financials'[ss],'Financials'[Invoice],Shipments[Invoice])&lt;=0.2,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp; &nbsp; LOOKUPVALUE('Financials'[ss],'Financials'[Invoice],Shipments[Invoice])&gt;=0.3),&nbsp;&nbsp;&nbsp; Cost*LOOKUPVALUE('Financials'[PC- Income],'Financials'[Invoice],Shipments[Invoice]),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LOOKUPVALUE('Financials'[PC- cost],&nbsp; 'Financials'[Invoice],Shipments[Invoice])),&nbsp;&nbsp;&nbsp; Cost*LOOKUPVALUE('Financials'[PC- Income],'Financials'[Invoice],Shipments[Invoice]))&nbsp;Now I am trying to create same formula for multiple customers with different Margins but it is not working&nbsp;IndexStart DateEnd Date&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; A&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; B&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; C&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; D11/1/20183/31/20190.2280.2090.2280.22824/1/20193/31/20200.2210.2090.2210.22134/1/20203/31/20210.2190.2090.2190.219&nbsp;Could anyone please help how to create formula for all customers, its very urgent and very important.&nbsp;I have tried using(CALCULATE (FIRSTNONBLANK('Financials'[Income],1),FILTER(ALL('Financials'), 'Scope'[Invoice] = 'Shipments'[Invoice]))instead of LOOKUP Function but still it is not working&nbsp;Please let me know if any other details are required&nbsp;Thanks in advance.

anonymous · Answer

Thanks for the reply.I have created “GetValue” measure and I got Values for A,B,C,DBelow is the formula for Customer ‘A’ and I got the result.PC-Cost =&nbsp;&nbsp; Var Cost = IF( Shipments[BilledOn]&lt; DATE(2019,04,01), (1-0.228), IF(Shipments[BilledOn]&gt; DATE(2019,04,01)&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; ||Shipments[BilledOn]&lt; DATE(2020,04,01), (1-0.221), (0.219)) )&nbsp;&nbsp; ReturnIF( LOOKUPVALUE('Financials'[Income],'Financials'[Invoice],Shipments[Invoice])-LOOKUPVALUE('Financials'[Cost],'Financials'[Invoice],Shipments[Invoice]) = LOOKUPVALUE('Financials'[Profit],'Financials'[Invoice],Shipments[Invoice]), IF(OR(LOOKUPVALUE('Financials'[ss],'Financials'[Invoice],Shipments[Invoice])&lt;=0.2, LOOKUPVALUE('Financials'[ss],'Financials'[Invoice],Shipments[Invoice])&gt;=0.3),Cost*LOOKUPVALUE('Financials'[PC- Income],'Financials'[Invoice],Shipments[Invoice]),LOOKUPVALUE('Financials'[PC- cost], 'Financials'[Invoice],Shipments[Invoice])),Cost*LOOKUPVALUE('Financials'[PC- Income],'Financials'[Invoice],Shipments[Invoice]))&nbsp;Now I have to create for all customers (ABCD)Could you please let me know how can I use ‘’GetValue” in the above formula

v-lianl-msft · Answer

Hi Anonymous&nbsp;,&nbsp;Could you please share sample data or sample .pbix?&nbsp;Best Regards,LiangIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

anonymous · Answer

Thanks for the response.&nbsp;Please let me know how to share data so that i can share sample data.&nbsp;&nbsp;

Forum Discussion

Creating Margin for mulitple customers

10 Replies

Related content

How creating a Python custom library

Creating a custom column

create custom column concatenated

Creating Custom columns

Creating a custom leaderboard

Index	Start Date	End Date	A	B	C	D
1	1/1/2018	3/31/2019	0.228	0.209	0.228	0.228
2	4/1/2019	3/31/2020	0.221	0.209	0.221	0.221
3	4/1/2020	3/31/2021	0.219	0.209	0.219	0.219