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 share the sample pbix file if possible.

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)

    • unknown917's avatar
      unknown917
      Helper IV
      1 year ago

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