topic Re: Count based on variable date range and start date in DAX Commands and Tips

Count based on variable date range and start date

unknown917 — Tue, 11 Mar 2025 16:34:51 GMT

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!

Re: Count based on variable date range and start date

techies — Tue, 11 Mar 2025 20:02:19 GMT

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.

Re: Count based on variable date range and start date

unknown917 — Tue, 11 Mar 2025 20:49:39 GMT

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

Re: Count based on variable date range and start date

techies — Fri, 14 Mar 2025 09:22:31 GMT

Hi unknown917 please share the sample pbix file if possible.

Re: Count based on variable date range and start date

unknown917 — Fri, 14 Mar 2025 11:36:03 GMT

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

Re: Count based on variable date range and start date

techies — Fri, 14 Mar 2025 18:56:04 GMT

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)

Re: Count based on variable date range and start date

unknown917 — Fri, 14 Mar 2025 20:39:01 GMT

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