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

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