How to Mirror this Excel SUMIFS Function in Custom Measure?

[Anonymous]

How can I mirror this function?

=SUMIFS(Table1[Price],Table1[Name],"<>Mike",Table1[Name],"<>Jon",Table1[Name],"<>Ethan",Table1[Ship Date],"<"&TODAY(),Table1[Ship Date],">"&DATE(YEAR(TODAY()),1,1),Table1[Part Number],"=SO*")

 Part Number    Ship Date      Name        Price 

10001277	2/1/2021	Emily	$     950.00
10001278	2/2/2021	Mike	$     970.00
10001279	2/3/2021	Mike	$     990.00
10001280	2/4/2021	Mike	$  1,010.00
10001281	2/5/2021	Emily	$  1,030.00
10001282	2/6/2021	Emily	$  1,050.00
10001283	2/7/2021	Emily	$  1,070.00
10001284	2/8/2021	Emily	$  1,090.00
10001285	2/9/2021	Emily	$  1,110.00
10001286	2/10/2021	Emily	$  1,130.00
10001287	2/11/2021	Emily	$  1,150.00
10001288	2/12/2021	Emily	$  1,170.00
10001289	2/13/2021	Emily	$  1,190.00
10001290	2/14/2021	Mike	$  1,210.00
10001291	2/15/2021	Jill	$  1,230.00
10001292	2/16/2021	Emily	$  1,250.00
10001293	2/17/2021	Emily	$  1,270.00
10001294	2/18/2021	Ethan	$  1,290.00
10001295	2/19/2021	Ethan	$  1,310.00
10001296	2/20/2021	Ethan	$  1,330.00
10001297	2/21/2021	Ethan	$  1,350.00
10001298	2/22/2021	Ethan	$  1,370.00
10001299	2/23/2021	Ethan	$  1,390.00
10001300	2/24/2021	Emily	$  1,410.00
10001301	2/25/2021	Emily	$  1,430.00
10001302	2/26/2021	Emily	$  1,450.00
10001303	2/27/2021	Emily	$  1,470.00
10001304	2/28/2021	Jill	$  1,490.00
10001305	3/1/2021	Jill	$  1,510.00
10001306	3/2/2021	Jill	$  1,530.00
10001307	3/3/2021	Jill	$  1,550.00
10001308	3/4/2021	Emily	$  1,570.00
10001309	3/5/2021	Emily	$  1,590.00
10001310	3/6/2021	Mike	$  1,610.00
10001311	3/7/2021	Emily	$  1,630.00
10001312	3/8/2021	Emily	$  1,650.00
10001313	3/9/2021	Emily	$  1,670.00
10001314	3/10/2021	Emily	$  1,690.00
10001315	3/11/2021	Emily	$  1,710.00
10001316	3/12/2021	Emily	$  1,730.00
10001317	3/13/2021	Emily	$  1,750.00
10001318	3/14/2021	Emily	$  1,770.00
10001319	3/15/2021	Emily	$  1,790.00
10001320	3/16/2021	Emily	$  1,810.00
10001321	3/17/2021	Emily	$  1,830.00
10001322	3/18/2021	Emily	$  1,850.00
10001323	3/19/2021	Emily	$  1,870.00
10001324	3/20/2021	Mike	$  1,890.00
10001325	3/21/2021	Emily	$  1,910.00
10001326	3/22/2021	Emily	$  1,930.00
10001327	3/23/2021	Emily	$  1,950.00
10001328	3/24/2021	Emily	$  1,970.00
10001329	3/25/2021	Emily	$  1,990.00
10001330	3/26/2021	Mike	$  2,010.00
10001331	3/27/2021	Emily	$  2,030.00
10001332	3/28/2021	Emily	$  2,050.00
10001333	3/29/2021	Emily	$  2,070.00
10001334	3/30/2021	Emily	$  2,090.00

[Watsky · Accepted solution]

Heya @Anonymous,

See if this works for you in a Calculated Column. None of the part numbers in your example data had "SO", so I did my testing by replacing SO with 10.  

SUMIFS =
CALCULATE (
    SUM ( 'Table'[Price] ),
    'Table'[Name] <> "Mike",
    'Table'[Name] <> "John",
    'Table'[Name] <> "Ethan",
    'Table'[Ship Date] < TODAY (),
    'Table'[Ship Date] > DATE ( YEAR ( TODAY () ), 1, 1 ),
    LEFT ( 'Table'[Part Number], 2 ) = "SO"
)

 

Did my answer(s) help you? Give it a kudos by clicking the Thumbs Up! ?
Did my post answer your question(s)? Mark my post as a solution. This will help others find the solution.