Finding the number of Occurrences Prior to a Date for each year by unique ID

[MSW]

Help, 

I need to find a way to count the number of occurances an ID receives a code prior from the first date the code is sent to the first purchase by the customer. 

Table 1 Example:

Customer ID	Code	Earliest Code Sent Date
123	001	1/1/2020
123	001	1/1/2020
123	002	1/1/2019
456	001	3/1/2020
456	002	3/1/2019
456	002	6/1/2019
789	001	1/1/2020
789	002	1/1/2019
789	001	3/1/2018

Table 2: 

customerID	Purchase Date	Earliest Purchase Date	Outcome from formula
123	1/10/2020	1/10/2020	2
123	2/1/2020	1/10/2020	2
123	2/1/2019	2/1/2019	1
456	7/1/2019	7/1/2019	2
456	10/1/2019	7/1/2019	2
456	1/2/2020	1/2/2020	1
789	4/1/2020	4/1/2020	1
789	3/1/2018	3/1/2018	1
789	5/1/2019	5/1/2019	1

The goal is to try to determine how many codes a customer receives before their first purchase each year. Any help would be much appreciated. Bonus if I can figure out how many codes are received between send dates and purchase dates. 

Thank you!

[v-jayw-msft · Accepted solution]

Hi @MSW ,

Please check the formulas.

Column = CALCULATE(MIN(Table1[Earliest Code Sent Date ]),FILTER(Table1,Table1[Customer ID]=Table2[customerID]&&YEAR(Table1[Earliest Code Sent Date ])=YEAR(Table2[Purchase Date])))

Column 2 = CALCULATE(COUNT(Table1[Code]),FILTER(Table1,Table1[Customer ID]=Table2[customerID]&&Table1[Earliest Code Sent Date ]>=Table2[Column]&&Table1[Earliest Code Sent Date ]<=Table2[Earliest Purchase Date]))

Best Regards,

Jay

[lbendlin]

Your expected outcome is incorrect for two entries, as the code was sent after the first purchase that year.

Table 1

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRMjAwBJKG+ob6RgZGBkqxOkRIGMElDC3BEiamZnAdxsg6YBJGcAk0HSAJM2QJcwtL7JbDJDAsR9YBtcNCKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer ID" = _t, Code = _t, #"Earliest Code Sent Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{ {"Earliest Code Sent Date", type date}})
in
    #"Changed Type"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Rename to Table 1

Table 2

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRMjAwBJKG+ob6RgZGBkqxOkRIGMElDC3BEiamZnAdxsg6YBJGcAk0HSAJM2QJcwtL7JbDJDAsR9YBtcNCKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer ID" = _t, Code = _t, #"Earliest Code Sent Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{ {"Earliest Code Sent Date", type date}})
in
    #"Changed Type"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Rename to Table 2