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 

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