Count based on variable date range and start date

[unknown917]

I have a table with a column that supplies a start date for a reopening of any given ID.  In another table I have the current and historical operating data for the ID by date with key items like sales qty.

I am in need of taking the reopening date as the starting point and calculate out in 28 day (4wk) increments the sales qty in each cycle before and since the reopen date. This is to run as a comparison of how the ID has performed before and since the repopen.

Since every reopen date can be different, how can I accomplish the date variability with the incremental summing or counting?

Thanks in advance!

[techies]

 Hi @unknown917 please share the sample pbix file if possible.

[unknown917]

My apologies, @techies  - I am unable to share a file, but here is a sample of the tables I'm referring to:

table 1
ID	ReOpenDate
1	1/6/2025
2	2/17/2025
3	1/13/2025
4	11/4/2024
5	3/10/2025

table 2
ID	Inv Date	Sales Qty
1	9/9/2024	150
1	9/12/2024	175
1	9/16/2024	205
1	9/19/2024	234
2	11/13/2024	17
2	1/15/2025	12
3	2/13/2025	100
3	2/20/2025	125
3	3/11/2025	123
4	3/12/2025	5
5	10/22/2024	500
5	10/24/2024	400

Intended result is to select the ID with a slicer and see performance after reopen:

ID	wk 1-4 (28days)	wk 5-8 (28days)	wk 9-12 (28 days)
1
2
3	0	225	123
4
5

Metrics before re-open date could also be valuable, not sure if that is also possible

[techies · Accepted solution]

Hi @unknown917 please try this

create a calculated column in your sales table for the week buckets 

Week_Bucket =

VAR ReOpenDate = LOOKUPVALUE('reopen'[Reopen Date], 'reopen'[ID], 'sales'[ID])

VAR DaysSince = DATEDIFF(ReOpenDate, 'sales'[Date], DAY)

RETURN

SWITCH(

    TRUE(),

    DaysSince >= 0 && DaysSince < 28, "Week 1-4 After",

    DaysSince >= 28 && DaysSince < 56, "Week 5-8 After",

    DaysSince >= 56 && DaysSince < 84, "Week 9-12 After",

    DaysSince < 0 && DaysSince >= -28, "Week 1-4 Before",

    DaysSince < -28 && DaysSince >= -56, "Week 5-8 Before",

    DaysSince < -56 && DaysSince >= -84, "Week 9-12 Before",

    "Other"

)

And then create the measure to sum the sales based on the buckets

Sales_by_Weeks =
VAR SelectedWeek = SELECTEDVALUE('SalesData'[Week_Bucket])
RETURN
CALCULATE(SUM('SalesData'[Sales Qty]), 'SalesData'[Week_Bucket] = SelectedWeek)

[unknown917]

Genius @techies !  I can make this work!  Thank you for your help!

[techies]

Hi @unknown917 

As I understand, create a RelativePeriod column that groups sales into 4-week periods before and after reopening by dividing the day difference by 28.

RelativePeriod =

VAR ReopenDate = LOOKUPVALUE(reopen[Reopen Date], reopen[ID], sales[ID])

VAR DaysDifference = DATEDIFF(ReopenDate, sales[Date], DAY)

RETURN INT(DaysDifference / 28)

And the Sales_4WeekPeriod measure then sums sales for each period, allowing performance comparison over time.

Sales_4WeekPeriod =

SUMX(

    VALUES(sales[RelativePeriod]),

    CALCULATE(

        SUM(sales[Sales Qty])

    )

)

Hope this works.

[unknown917]

Thanks, @techies  - 

this resulted in returning all sales for the entire date range in 2nd table.  I need to be able to represent the 4 week increments, based on the go -live date