Analyzing 3 Months Before/After a Date w/Date Being Different For Different Rows

[bvbull200]

I have two data tables.  The first is a list of part #s with each transaction against them over the past 18 months.  Each part # is listed multiple times due to being transacted against on multiple occasions.  I have the date of each transaction.

The second is a list of each of those part #s with a modified date.  Each part # is listed once and the modified date indicates when a particular enhancement went in to place.  Each part was modified on a different date.

What I need to do is sum all of the transactions for each part for the 3 months prior to the modification date and the 3 months after the modification date.

For instance, if a part was modified on 07/01/2019, I would need to know the sum of transactions from 04/01/2019 - 06/30/2019 and separately, the sum of transactions from 07/01/2019 - 09/30/2019.

For a different part, it might have been modified on 09/16/2019.  I would need to know the sum of transactions from 06/16/2019 - 09/15/2019 and separately, the sum of transactions from 09/16/2019 - 12/15/2019.

Ideally, I would be able to have that 3 month before/after be a dynamic number that I can select via a slider or something, so if I wanted to look at 6 months before/after, I could do that on the fly.  If it is static and has to be changed via the measure, that is okay, too.

Here is a sample of data to work with.  I appreciate any bright ideas to help solve this.

Transaction Table

Part #	TransactionValue	Transaction Date
Umbrella	150	1/1/2019
Umbrella	150	1/1/2019
Umbrella	75	1/1/2019
Umbrella	75	1/1/2019
Umbrella	66.97	2/1/2019
Umbrella	378	2/1/2019
Umbrella	139.1	2/1/2019
Umbrella	70.5	2/1/2019
Umbrella	75	3/1/2019
Umbrella	72.73	4/1/2019
Umbrella	251.92	4/1/2019
Umbrella	67.5	4/1/2019
Umbrella	75	4/1/2019
Umbrella	693	4/1/2019
Umbrella	126	5/1/2019
Umbrella	66.15	5/1/2019
Umbrella	126	5/1/2019
Umbrella	251.92	8/1/2019
Umbrella	67.5	8/1/2019
Umbrella	75	8/1/2019
Umbrella	693	8/1/2019
Umbrella	126	9/1/2019
Umbrella	66.15	9/1/2019
Umbrella	126	9/1/2019
Umbrella	251.92	9/1/2019
Umbrella	67.5	9/1/2019
Umbrella	75	9/1/2019
Umbrella	693	10/1/2019
Umbrella	126	10/1/2019
Umbrella	66.15	10/1/2019
Umbrella	126	10/1/2019
Umbrella	251.92	10/1/2019
Umbrella	67.5	10/1/2019
Umbrella	75	11/1/2019
Umbrella	693	11/1/2019
Umbrella	126	11/1/2019
Umbrella	66.15	11/1/2019
Umbrella	126	11/1/2019
Umbrella	251.92	11/1/2019
Umbrella	67.5	11/1/2019
Umbrella	75	12/1/2019
Umbrella	693	12/1/2019
Umbrella	126	12/1/2019
Umbrella	66.15	12/1/2019
Umbrella	126	12/1/2019
Umbrella	251.92	12/1/2019
Umbrella	67.5	12/1/2019
Umbrella	75	12/1/2019
Umbrella	693	12/1/2019
Toaster	79.28	1/1/2019
Toaster	1004.85	1/1/2019
Toaster	134.57	2/1/2019
Toaster	90.85	2/1/2019
Toaster	183.6	2/1/2019
Toaster	69.73	2/1/2019
Toaster	82.91	2/1/2019
Toaster	79.13	2/1/2019
Toaster	61.25	3/1/2019
Toaster	151.96	4/1/2019
Toaster	102.68	4/1/2019
Toaster	370.32	4/1/2019
Toaster	112.9	5/1/2019
Toaster	153.85	5/1/2019
Toaster	575.88	5/1/2019
Toaster	72.29	5/1/2019
Toaster	154.35	5/1/2019
Toaster	734.58	6/1/2019
Toaster	1022.87	8/1/2019
Toaster	376.87	8/1/2019
Toaster	144.4	8/1/2019
Toaster	318.17	9/1/2019
Toaster	69.23	9/1/2019
Toaster	61.35	9/1/2019
Toaster	184.21	9/1/2019
Toaster	330.02	9/1/2019
Toaster	111.68	9/1/2019
Toaster	48.02	10/1/2019
Toaster	273.08	10/1/2019
Toaster	139.66	10/1/2019
Toaster	60.83	10/1/2019
Toaster	991.68	10/1/2019
Toaster	80.19	10/1/2019
Toaster	327.24	11/1/2019
Toaster	118.19	11/1/2019
Toaster	1004.85	11/1/2019
Toaster	65.36	11/1/2019
Toaster	76.14	11/1/2019
Toaster	54	11/1/2019
Toaster	51.57	11/1/2019
Toaster	75.15	11/1/2019
Toaster	91.35	11/1/2019
Toaster	90.32	11/1/2019
Toaster	550.75	11/1/2019
Toaster	161.7	12/1/2019
Toaster	180.05	12/1/2019
Toaster	111.6	12/1/2019
Toaster	59.06	12/1/2019
Toaster	87.9	12/1/2019

Modified Date Table

Part	Modified Date
Umbrella	8/1/2019
Toaster	4/1/2019

[amitchandak]

When data is grouped at part level, this can help

calculate(sum(Transaction[TransactionValue]),all('Modified Date Table'), 
		filter(Transaction, Transaction[Transaction date]>= minx('Modified Date Table',dateadd('Modified Date Table'[Modified Date]),-3,Month)	
			&& Transaction[Transaction date]<= minx('Modified Date Table',dateadd('Modified Date Table'[Modified Date]),3,Month)))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[bvbull200]

Hmmm...I'm running in to a number of errors with that.  At first it was syntax, but I have fixed that.  Now it is saying that there are too few variables for DateAdd.

I'll keep tinkering with it.

[amitchandak]

Share you current formula

[bvbull200]

Measure = calculate(sum(Transaction[TransactionValue]),all('Modified Date Table'), 
		filter(Transaction, Transaction[Transaction date]>= minx('Modified Date Table',dateadd('Modified Date Table'[Modified Date]),-3,Month)	
			&& Transaction[Transaction date]<= minx('Modified Date Table',dateadd('Modified Date Table'[Modified Date]),3,Month)))

The error is:

Too few arguments were passed to the DATEADD function. The minimum argument count for the function is 3.

[amitchandak · Accepted solution]

@bvbull200, Try

Measure = calculate(sum(Transaction[TransactionValue]),all('Modified Date Table'), 
		filter(Transaction, 
			Transaction[Transaction date]>= minx('Modified Date Table',dateadd('Modified Date Table'[Modified Date],-3,Month))	
			&& Transaction[Transaction date]<= minx('Modified Date Table',dateadd('Modified Date Table'[Modified Date],3,Month))
			
			)
)

OR
Rolling 6 from -3= CALCULATE(sum(Transaction[TransactionValue]),DATESINPERIOD('Modified Date Table'[Date],ENDOFMONTH(dateadd('Modified Date Table'[Date],-3,MONTH)),6,MONTH))