want to see sum of value before and after a certain date

[Sut_Datanaut]

I have a standard table that has a date column and a unique id column that i am doing count of to aggregate and see number of unique id's by day. 

i also have a seperate table that has a list of events, and their start and end dates. 

What i would like to create is a way to capture the total number of unique ID's for the past 6 weeks before the start date, and another value for the 6 weeks after the start date. 

the tricky part is that i want to use a slicer to change which event i want to see the totals for. 

so essentially the sum of count(unique id's) from 6 weeks back until the selected start date and the sum of count(unique id's) from the start date to 6 weeks in the future. 

Thank you, 

[VahidDM]

Hi @Sut_Datanaut 

Can you post sample data as text and expected output?
Not enough information to go on;

please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables

Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/

[Sut_Datanaut]

Hey Vahid, 

Below are samples of the two tables, essentially if i have an event, i want to see the sum of the count of unique ID's for a lets say a week before, and then the sum of the count of unique ID's for a week after an event date. 

so for this example, if i select event 2 in the slicer, then i want one formula that sums the the count of unique ID's for a week before 1/6/2023 (the start date of event 2) and another than sums a week after 1/6/2023

[Ashish_Mathur]

Hi,

Share the download link of the MS Excel file.

[Sut_Datanaut]

dont have one drive, but here is a sample of the two tables

Date	Unique ID
1/1/2023	65NE5N6E5N6E56N
1/1/2023	ERSEVERT5VSEE
1/1/2023	SV5SEVSE5EV
1/2/2023	S5EVSEVEEVS
1/2/2023	56NE5E56N5E
1/2/2023	56NE5E56N5E
1/3/2023	65N5E6N56N
1/3/2023	65N5E6N56N
1/4/2023	N76N6RE6
1/4/2023	N5Y65N566
1/5/2023	WN6W4NW4N
1/5/2023	W34F3WF
1/6/2023	NW6WNWN
1/6/2023	W5GW45G
1/7/2023	W5G45GW4
1/7/2023	W5G45GW4
1/8/2023	45WG45GW45G
1/8/2023	45WG45GW45G
1/9/2023	34G3WGW5
1/9/2023	34G3WGW5
1/10/2023	4F432324324F
1/10/2023	4F432324324F
1/11/2023	234F234234
1/11/2023	234F234234
1/12/2023	234F3GG45
1/12/2023	234F3GG45
1/12/2023	234F3GG45
1/13/2023	45G43G5345G
1/13/2023	45G43G5345G
1/13/2023	45G43G5345G
1/14/2023	43G5345G4F
1/14/2023	43G5345G4F
1/14/2023	43G5345G4F
1/15/2023	F34FF434
1/15/2023	F34FF434
1/15/2023	F34FF434
1/16/2023	X4XQ4T4XQ3
1/16/2023	X4XQ4T4XQ3
1/16/2023	X4XQ4T4XQ3
1/17/2023	Q3X4XX4FQ4
1/17/2023	Q3X4XX4FQ4
1/17/2023	Q3X4XX4FQ4
1/18/2023	Q43X34F5VYVW4
1/18/2023	Q43X34F5VYVW4
1/18/2023	Q43X34F5VYVW4
1/19/2023	V4W4VW4VW4V
1/19/2023	V4W4VW4VW4V
1/19/2023	V4W4VW4VW4V
1/20/2023	65NE5N6E5N6E56N
1/20/2023	65NE5N6E5N6E56N
1/20/2023	65NE5N6E5N6E56N
1/21/2023	56NE5E56N5E
1/21/2023	56NE5E56N5E
1/21/2023	56NE5E56N5E

Start Event	End Event	Event Name
1/16/2023	1/23/2023	Event 1
1/24/2023	2/1/2023	Event 2

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.