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 sale...
  • 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)

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