Creating Margin for mulitple customers

[Anonymous]

Hi there,

I have created a logic for one of the customer “A” with the below formula and it is working fine.

PickUpCost-Cost =

    Var Cost = IF(Shipments[BilledOn]< DATE(2019,04,01), (1-0.228),

                      IF(Shipments[BilledOn]> DATE(2019,04,01) ||Shipments[BilledOn]< DATE(2020,04,01), (1-0.221), (0.219)))            

    Return

    IF(

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])<=0.2,

            LOOKUPVALUE('Financials'[ss],'Financials'[Invoice],Shipments[Invoice])>=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]))

Now I am trying to create same formula for multiple customers with different Margins but it is not working

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

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 working

Please let me know if any other details are required

Thanks in advance.

[V-lianl-msft]

Hi @Anonymous ,

Could you please share sample data or sample .pbix?

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks for the response.

Please let me know how to share data so that i can share sample data. 

[V-lianl-msft]

Hi @Anonymous ,

Screenshot,Copy and paste sample data in a table,Cloud service like OneDrive for business.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Please find sample data below:

Invoice	PC - Income	PC - Cost	PC - Profit	Billed On	ss	Customer
200106470	73.70	57.56	16.14	4/3/2020	21.9%	A
200106240	57.62	45.00	12.62	4/3/2020	21.9%	A
200106210	160.05	125.00	35.05	4/3/2020	21.9%	A
200106148	57.62	45.00	12.62	4/3/2020	21.9%	A
200105861	420.28	328.24	92.04	4/3/2020	21.9%	A
190093692	1,753.53	1,613.18	140.35	3/16/2020	20.3%	B
190095887	802.10	737.90	64.20	3/16/2020	20.3%	B
190093348	802.10	737.90	64.20	3/16/2020	20.3%	B
200105535	839.83	772.61	67.22	3/27/2020	20.3%	B
200105561	398.89	366.96	31.93	3/27/2020	20.3%	B
200104361	102.72	80.00	22.72	3/20/2020	22.1%	C
200105982	57.78	45.00	12.78	4/1/2020	22.1%	C
200105983	192.60	150.00	42.60	4/1/2020	22.1%	C
200105984	205.44	160.00	45.44	4/1/2020	22.1%	C
200106396	6,123.05	4,880.07	1,242.98	4/10/2020	21.9%	D
200106060	3,756.59	2,994.00	762.59	4/10/2020	21.9%	D
200106064	3,658.85	2,916.10	742.75	4/10/2020	21.9%	D
200105854	4,381.79	3,492.29	889.50	4/10/2020	21.9%	D

Margin Table:

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

Expecting Output is PC Cost with below margin according to date and customer.

Please find below table for the refernece which i have worked earlier and got the Expexted PC Cost

Invoice	Pickup Costs - Income	Pickup Costs - Cost	Pickup Costs - Profit	Billed On	ss	Customer	Expected PC Cost
190067733	482.51	375.79	106.72	9/20/2019	0.22	A	375.8753
190067742	188.36	146.70	41.66	5/17/2019	0.22	A	146.7324
190063472	87.59	68.22	19.37	4/19/2019	0.22	A	68.23261
190073208	57.78	45.00	12.78	8/30/2019	0.22	A	45.01062
190073979	96.30	75.00	21.30	8/30/2019	0.22	A	75.0177
190076055	57.78	45.00	12.78	9/27/2019	0.22	A	45.01062
190076296	1177.94	917.40	260.54	9/20/2019	0.22	A	917.6153
190077854	428.88	334.10	94.78	8/8/2019	0.22	A	334.0975
190080425	57.78	45.00	12.78	8/30/2019	0.22	A	45.01062
190083738	403.55	314.29	89.26	10/10/2019	0.22	A	314.3655
190083671	204.40	159.19	45.21	10/10/2019	0.22	A	159.2276
190083933	151.29	117.83	33.46	10/10/2019	0.22	A	117.8549
190084277	545.70	425.00	120.70	8/30/2019	0.22	A	425.1003
190065866	381.62	297.21	84.41	9/6/2019	0.22	A	297.282
190065712	143.86	112.04	31.82	9/6/2019	0.22	A	112.0669
190085442	124.79	97.19	27.60	10/4/2019	0.22	A	97.21141
190083281	96.30	75.00	21.30	10/10/2019	0.22	A	75.0177
190086181	256.80	200.00	56.80	10/11/2019	0.22	A	200.0472
190081184	288.90	225.00	63.90	10/28/2019	0.22	A	225.0531
190086651	644.62	502.04	142.58	12/20/2019	0.22	A	502.159
190087805	57.78	45.00	12.78	10/4/2019	0.22	A	45.01062
190088671	551.48	429.50	121.98	10/4/2019	0.22	A	429.6029
190088637	179.79	140.02	39.77	10/4/2019	0.22	A	140.0564
190089021	417.30	325.00	92.30	11/1/2019	0.22	A	325.0767
190094008	288.90	225.00	63.90	12/13/2019	0.22	A	225.0531
190093973	465.04	362.18	102.86	12/13/2019	0.22	A	362.2662
190095128	108.27	84.32	23.95	12/13/2019	0.22	A	84.34233
190095396	57.78	45.00	12.78	12/13/2019	0.22	A	45.01062
190095382	703.12	547.60	155.52	12/13/2019	0.22	A	547.7305

Margin Table:

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

Im looking for "EXPECTED PC COST" for all customers.

[Anonymous]

Hi there,

I have created a logic for one of the customer “A” with the below formula and it is working fine.

PickUpCost-Cost =

    Var Cost = IF(Shipments[BilledOn]< DATE(2019,04,01), (1-0.228),

                      IF(Shipments[BilledOn]> DATE(2019,04,01) ||Shipments[BilledOn]< DATE(2020,04,01), (1-0.221), (0.219)))            

    Return

    IF(

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])<=0.2,

            LOOKUPVALUE('Financials'[ss],'Financials'[Invoice],Shipments[Invoice])>=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]))

Now I am trying to create same formula for multiple customers with different Margins but it is not working

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

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 working

Please let me know if any other details are required

Thanks in advance.

[MFelix]

Hi   @Anonymous ,

What is the value you are trying to return on the lookupvalue?

Believe that your measurements is complicated and may be made in a different way however there is not very much information about your model so the reading of your measure is complicated with the given data.

Also believe that if you want to have this in a dinamice way you need to unpivot the table you present and as a consequence change the formula you presented.

Can you share a sample file and expected values?

[Anonymous]

Hi Felix,
Thanks for the reply.
I am unable to attach file here. Could you please let know how to share sample data or pbix file, so that i can explain you clearly what iam looking for.

I have created logic for PC Cost with margin for one customer and it is working fine.

Now iam looking for PC Cost for all customers combined.

[V-lianl-msft]

Hi @Anonymous ,

You could upload your sample .pbix to a cloud storage service (like OneDrive for business) and paste the link to that storage location in the post.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bheepatel]

Hi @Anonymous 

One possible solution is to pivot your table so that you have your table in the same format as Table B below. You can use the pivot options available to you in Power Query.

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.

Hope this helps!

[Anonymous]

Thanks for the reply.

I have created “GetValue” measure and I got Values for A,B,C,D

Below is the formula for Customer ‘A’ and I got the result.

PC-Cost =
   Var Cost = IF( Shipments[BilledOn]< DATE(2019,04,01), (1-0.228), IF(Shipments[BilledOn]> DATE(2019,04,01)       

                       ||Shipments[BilledOn]< DATE(2020,04,01), (1-0.221), (0.219)) )
   Return
IF
( 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])<=0.2, LOOKUPVALUE('Financials'[ss],'Financials'[Invoice],Shipments[Invoice])>=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]))

Now I have to create for all customers (ABCD)
Could you please let me know how can I use ‘’GetValue” in the above formula