Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to Mirror this Excel SUMIFS Function in Custom Measure?

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
100012782/2/2021Mike $     970.00
100012792/3/2021Mike $     990.00
100012802/4/2021Mike $  1,010.00
100012812/5/2021Emily $  1,030.00
100012822/6/2021Emily $  1,050.00
100012832/7/2021Emily $  1,070.00
100012842/8/2021Emily $  1,090.00
100012852/9/2021Emily $  1,110.00
100012862/10/2021Emily $  1,130.00
100012872/11/2021Emily $  1,150.00
100012882/12/2021Emily $  1,170.00
100012892/13/2021Emily $  1,190.00
100012902/14/2021Mike $  1,210.00
100012912/15/2021Jill $  1,230.00
100012922/16/2021Emily $  1,250.00
100012932/17/2021Emily $  1,270.00
100012942/18/2021Ethan $  1,290.00
100012952/19/2021Ethan $  1,310.00
100012962/20/2021Ethan $  1,330.00
100012972/21/2021Ethan $  1,350.00
100012982/22/2021Ethan $  1,370.00
100012992/23/2021Ethan $  1,390.00
100013002/24/2021Emily $  1,410.00
100013012/25/2021Emily $  1,430.00
100013022/26/2021Emily $  1,450.00
100013032/27/2021Emily $  1,470.00
100013042/28/2021Jill $  1,490.00
100013053/1/2021Jill $  1,510.00
100013063/2/2021Jill $  1,530.00
100013073/3/2021Jill $  1,550.00
100013083/4/2021Emily $  1,570.00
100013093/5/2021Emily $  1,590.00
100013103/6/2021Mike $  1,610.00
100013113/7/2021Emily $  1,630.00
100013123/8/2021Emily $  1,650.00
100013133/9/2021Emily $  1,670.00
100013143/10/2021Emily $  1,690.00
100013153/11/2021Emily $  1,710.00
100013163/12/2021Emily $  1,730.00
100013173/13/2021Emily $  1,750.00
100013183/14/2021Emily $  1,770.00
100013193/15/2021Emily $  1,790.00
100013203/16/2021Emily $  1,810.00
100013213/17/2021Emily $  1,830.00
100013223/18/2021Emily $  1,850.00
100013233/19/2021Emily $  1,870.00
100013243/20/2021Mike $  1,890.00
100013253/21/2021Emily $  1,910.00
100013263/22/2021Emily $  1,930.00
100013273/23/2021Emily $  1,950.00
100013283/24/2021Emily $  1,970.00
100013293/25/2021Emily $  1,990.00
100013303/26/2021Mike $  2,010.00
100013313/27/2021Emily $  2,030.00
100013323/28/2021Emily $  2,050.00
100013333/29/2021Emily $  2,070.00
100013343/30/2021Emily $  2,090.00
  • Watsky's avatar
    Watsky
    5 years ago

    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.

1 Reply

  • Watsky's avatar
    Watsky
    Icon for Solution Sage rankSolution Sage
    5 years ago

    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.