Forum Discussion

unknown917's avatar
unknown917
Helper IV
1 year ago
Solved

Count based on variable date range and start date

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's avatar
    techies
    1 year ago

    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)

6 Replies

  • techies's avatar
    techies
    Super User
    1 year ago

    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's avatar
      unknown917
      Helper IV
      1 year ago

      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

    • unknown917's avatar
      unknown917
      Helper IV
      1 year ago

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

       

      table 1
      IDReOpenDate
      11/6/2025
      22/17/2025
      31/13/2025
      411/4/2024
      53/10/2025
      table 2
      IDInv DateSales Qty
      19/9/2024150
      19/12/2024175
      19/16/2024205
      19/19/2024234
      211/13/202417
      21/15/202512
      32/13/2025100
      32/20/2025125
      33/11/2025123
      43/12/20255
      510/22/2024500
      510/24/2024400

       

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

      IDwk 1-4 (28days)wk 5-8 (28days)wk 9-12 (28 days)
      1   
      2   
      30225123
      4   
      5   

       

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

      • techies's avatar
        techies
        Super User
        1 year ago

        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)