Sum sales units between 2 dates for each item number

[Anonymous]

I'm still very green but I have the following columns thus far and I want to sum Sales Units between the "First Date of Sale" and "2 Week Date" (including the first and last date for the full 14 days) for each unique item number.

Item Number	Sales Date	Sales Units	First Date of Sale	2 Week Date
2554	01/01/20	7	01/01/20	01/14/20
2554	01/10/20	1	01/01/20	01/14/20
2554	01/22/20	3	01/01/20	01/14/20
3996	02/01/20	4	02/01/20	02/14/20
7884	03/01/20	3	03/01/20	03/14/20
7884	03/06/20	2	03/01/20	03/14/20
3996	02/22/20	6	02/01/20	02/14/20

My end goal is to have it look like this:

Item Number	First Date of Sale	Sales First 2 Weeks
2554	01/01/20	8
3996	02/01/20	4
7884	03/01/20	5

[dax · Accepted solution]

Hi @Anonymous , 

You also could try below measure to see whether  it work or not

Measure 2 = CALCULATE(SUM(t2[Sales Units]), FILTER(t2, t2[Sales Date]>=t2[First Date of Sale]&& t2[Sales Date]<=t2[First Date of Sale]+14))

If you want to use M code, you could try below M code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY5LCsAgDETvkrVQk/g9i+T+12hsqFZapCA4k/cW0xpQjAEceDz0kdeY16o/hh7FPW30hvGXTWSYNzbXmvqNBg5r1TjtXMqFeWBeq8ZPOxmmjT2X3LvTa4lFkRM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Item Number" = _t, #"Sales Date" = _t, #"Sales Units" = _t, #"First Date of Sale" = _t, #"2 Week Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item Number", Int64.Type}, {"Sales Date", type date}, {"Sales Units", Int64.Type}, {"First Date of Sale", type date}, {"2 Week Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Sales Date]>=[First Date of Sale] and [Sales Date]<=[2 Week Date] then [Sales Units] else 0),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Item Number"}, {{"sum", each List.Sum([Custom]), type number}, {"all", each _, type table [Item Number=number, Sales Date=date, Sales Units=number, First Date of Sale=date, 2 Week Date=date, Custom=number]}}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Sales Date", "Sales Units", "First Date of Sale", "2 Week Date"}, {"Sales Date", "Sales Units", "First Date of Sale", "2 Week Date"})
in
    #"Expanded all"

or 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY5LCsAgDETvkrVQk/g9i+T+12hsqFZapCA4k/cW0xpQjAEceDz0kdeY16o/hh7FPW30hvGXTWSYNzbXmvqNBg5r1TjtXMqFeWBeq8ZPOxmmjT2X3LvTa4lFkRM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Item Number" = _t, #"Sales Date" = _t, #"Sales Units" = _t, #"First Date of Sale" = _t, #"2 Week Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item Number", Int64.Type}, {"Sales Date", type date}, {"Sales Units", Int64.Type}, {"First Date of Sale", type date}, {"2 Week Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Sales Date]>=[First Date of Sale] and [Sales Date]<=[2 Week Date] then [Sales Units] else 0),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Item Number", "First Date of Sale"}, {{"sum", each List.Sum([Custom]), type number}})
in
    #"Grouped Rows"

Best Regards,
Zoe Zhi

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

[az38]

Hi @Anonymous 

try create a measure

Measure = CALCULATE( SUM(Table[Sales Units]),
FILTER(ALL(Table), Table[Sales Date] >= SELECTEDVALUE(Table[First Date of Sale]) && Table[Sales Date] <= SELECTEDVALUE(Table[2 Week Date]) )
)

[Anonymous]

What if I want to do it in Power Query by adding a column? The reason is becasue right now the data I'm working with is 65 million rows and I want to condense it down to about 20K when it's all added together and then allow co-workers to use it in a pivot table in excel as that's all they're familiar with

[Greg_Deckler]

Sales First 2 Weeks = 

  VAR __FirstDate = MAX('Table'[First Date of Sale])

  VAR __LastDate = MAX('Table'[2 Week Date])

RETURN

  SUMX(FILTER('Table',[Sales Date] >= __FirstDate && [Sales Date] <= __LastDAte),[Sales Units])

Put that measure into a table visual along with Item Number (do not aggregate) and max first date of sale.